SQL Server Management Studio allows you to view effective permissions on an object, but it’s limited in a few important respects. To work around some of those limitations, I’ve built a stored procedure to display all the defined and effective permissions across an entire SQL Server database.
The three concepts
There are essentially three key concepts to keep track of when working with SQL Server security: principals, permissions and securables. The principals are the logins and users, the holders of permissions. The permissions are the “verbs”, specifying what the principals are allowed to do. Finally, securables are the actual objects, like databases, tables or procedures, to which you give permissions.
Principals, permissions and securables can all inherit each other. A principal could for instance be a group or a role, and will confer its permissions on to its group/role members. One permission can imply a number of other permissions – SELECT, for instance, requires you to also have VIEW DEFINITION rights to the object. Securables are also arranged in a hierarchy, with the server owning databases, which in turn own schemas that own objects, and so on.
To make things even more complicated, if you have multiple conflicting permissions (DENY and GRANT), the strictest rule applies, meaning that the effective permission is DENY.
You can imagine this three-dimensional inheritance as a cube, where principals, permissions and securables form one dimension each:
Effective permissions in Management Studio
Right-click an object in Management Studio, select Properties and click the “Permissions” tab, and you’ll see the assigned permissions at the bottom. If you click the tab “Effective”, you’ll see the effective rights of a given principal on the selected securable:
Now, there are some problems here, including but not limited to:
- You’ll have to select specific principals, you can’t really view all principals with rights.
- You can only select database principals for database securables, meaning you’ll have to figure out the server principal that owns the database principal.
- Principals with server-level administrative permissions don’t neccessarily have to have database-level principals, and won’t show up in this list.
- Windows users are only displayed if they have a registered login, not if they’re members of a Windows group with a login – you’ll have to figure out the Windows group memberships on your own.
- There’s no explanation as to which declared permission or role membership that caused this effective permission.
- And, perhaps most important if you’re doing security audits, there’s no export functionality.
In an attempt to fix those problems, I’ve written a stored procedure that will display all declared and effective permissions on all server-level and database-level securables.
Some neat features:
- Looks up Windows users (without logins) that are members of Windows groups (with logins) using xp_logininfo.
- Shows “paths” for principals, securables and permissions – so you’ll know how this effective permission came to be.
- Includes implicit CONTROL permissions for owners of securables.
The script accepts three filter parameters:
- @principal: allows filtering on specific principals by name, using a wildcard pattern.
- @securable: allows filtering on specific securables by name, using a wildcard pattern. All securables that are not schema-bound are prefixed, for instance DATABASE::[WideWorldImporters], SCHEMA::[Application] or ENDPOINT::[TSQL Named Pipes].
- @permission: allows filtering on permission names, using a wildcard pattern.
You can format the output in three different ways:
- @permission_list=0, @output_xml=0: returns a table with one line for each combination of effective principal, securable and permission. This is the default setting.
- @permission_list=1, @output_xml=0: returns a table with all combinations of effective principals, securables and permissions, but combines permissions in a comma-separated list, making the list a bit more human-readable. However, this reduces the detail of the permission_path column.
- @output_xml=1: returns one XML blob with all declared permissions and their respective effective permissions. The blob can be retrieved using the @xml OUTPUT variable.
I hope this will come in handy for all you guys who do security audits or just want to know who actually has administrative permissions on things.
Who has full administrative permissions on the current database:
EXECUTE dbo.sp_help_permissions @securable='DATABASE::%', @permission='CONTROL';
Who can connect to the instance:
EXECUTE dbo.sp_help_permissions @permission='CONNECT SQL';
All effective permissions for a specific login/user:
EXECUTE dbo.sp_help_permissions @principal='domain\name', @permission_list=1;
Who can impersonate another login or user:
EXECUTE dbo.sp_help_permissions @permission='IMPERSONATE';
Get the script from the Downloads page.
Security concepts get you all confused? Should should probably spend a few minutes reading up on it. Check out my series on SQL Server security: part 1, part 2, part 3 and part 4.
12 thoughts on “Effective permissions on SQL Server”
Pingback: Effective Permissions In SQL Server – Curated SQL
Got a “Violation of PRIMARY KEY constraint ‘PK__#BA674E8__E400D3A4F01CE2E7’. Cannot insert duplicate key in object ‘dbo.@srv_principals’ ” (I just ran sp_help_permissions.)
FYI: a windows login is defined in every database with some databaseroles and a serverrole
Thanks! I’ll see if I can fix the problem.
added some debug info, it’s in this part:
INSERT INTO @srv_principals (principal_id, [type_desc], [sid], [name])
SELECT DISTINCT -DENSE_RANK() OVER (ORDER BY account_name) AS principal_id, N’WINDOWS_LOGIN’, NEWID(), account_name
WHERE account_name NOT IN (SELECT [name] FROM @srv_principals WHERE [type_desc]=N’WINDOWS_LOGIN’);
Ok, so obviously, expecting NEWID() to work with a DISTINCT was.. naïve of me. Anyway, please help yourself to my hopefully-fixed version here: http://go.strd.co/sp_help_permissions
And thanks for the debug info!
Thanks. This version is working fine.
I get the same primary key violation that Wilfred reported. This seems like it would be a useful utility. Please let us know when it’s been corrected. Thanks!
I think I found and fixed the problem. You can download the most recent version here: http://go.strd.co/sp_help_permissions
Thanks for your help!
Great Article, series, and very useful SP.
Thanks! Glad you liked it.
That procedure just saved me hours! Thanks a lot.
Awesome! Glad I could help.