You have a user database with bunch of users and database roles and other database principla types. You need to have an insight into the rights assignments. You would like to know what rights are assigned to particular database principal.
The above task can be quite tricky as the rights on database objects can be granted or denied directly to particular database principal. However each of the database principal can have rights assignment inherited through database or application roles and there can be even a hierarchy of roles membership.
Getting a clear overview about the rights assignment in such situation is very problematic.
sp_HelpRights procedure can bring you a clear overview about the rights assignments as it lists all the rights granted/revoked toindividual database principals even thoseinherited through roles hierarchy including the complete inheritance path.
Anyway, do not mess this with effective rights for particular user. This is not aim of the stored procedure.
If you want to see effectiverights for particular user, use SSMS princiapl properties to list those (although even this is not fully accurate as it does not reflect rights assigned through server roles membership and many other).
As mentioned above the sp_HelpRights procedure returns an overview of rights assignments in the database.
You can find full source code of the sp_HelpRights as part of my SQL-Scripts project on GitHub in the RightsManagement folder. The script marks the stored procedure as system object so it can run in the context of the current user database.
The syntax for the sp_HelpRights is quite sraighforward:
To display help for the stored procedure invoke
The stored procedure has only two optional parameters
A Comma separated list of the databases for which the rights overview should be generated. Default value
NULL means current database.
The parameter suport wildcards and exclusion if [-] is used as first character of the name or wildcard.
|%,-m%||All databases except databases starting with m|
|DBA, User%, -User1%||Dtabase [DBA] and all databases starting with User but not starting with User1|
|?||Print help for the sp_HelpRights|
A comma separated list of database principals forwhich the rights overview should be printed. The default value
NULL means all database principals
Again the parameter supports wildcards and the syntax is the same as for the
Invoking the procedure without any parameters shows an overview for the current database and a sample output looks like below:
The output provides overview about all permissions assignments between database objects and database principals and mentioned at the beginnin of the post.
It lists all database principasl on which the permission has some effect even the permission was not GRANTED/DENIED to it, the original grantee (database principal to which the permission was originally granted), a complete inheritance path and as well the database principal which granted/denied the permission.
Let’s look on the below output:
In the First line of the Sample 1 we have a right assignment to the
DATABASE. the Right is
CONNECT and the state is
GRANT. The rigth assignment has effect on the windows user
DatabasePrincipalName = 'NT SERVICE\ReportServer'. We can see that the original grantee of that permission is also the
NT SERVICE\Report Server and this means that the permission ws granted directly to that user. it is also clearly visible in the
PermissionInheritancePath column as there is only name of that datbase principal. We can also see that the permission was granted by
On the second line of Sample 1 we have a different case. We see a
EXECUTE permission on the
dbo.CreateSegmentedChunk stored procdure. We can clearly see, that this permission has effect on the
DatabasePrincipalName = 'NT SERVICE\ReportServer'. But we can also clearly see, that this permission was not directly granted to the
NT SERVICE\ReportServer, but insted the original grantee was
RSExecRole database role. The
PermissionInheritancePath also shows that that permission was granted to the
NT SERVICE\ReportServer directly from the
RSExecRole database role.
The Sample2 shows a case where there are multiple levels in the inheritance. Wecan see, that the
SELECT permission on the user table
dbo.TestTable has effect on the
TestUser datbase principal. But we can clearly see that this permission was not directly granted to that user, bu theoriginal grantee of that permission is database role
PermissionInheritancePath we can alsosee, that the permission has efect on the
TestUser through membership in the
DBRoleB database role and that the database role
DBRoleB is member of the
DBRoleA. Therefore the complete inheritance path is
DBRoleA => DBRoleB => TestUser.
As mentioned through the article as as can be seen in the samples, the output of the stored procedure can bring you a clear insigths into rights assignments in your database environment and can be very helpfull for database rights auditing to get clear view of who has or does not has acces where, even there are multiple levels of complex roles membership.
Anyway, the one looking on the output must be clear, that the output does not provide information about the effective permissions of listed database principals.
Any somments and/or suggestions are welcomed.