Effective permissions on SQL Server

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.

Inheritance

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.

sp_help_permissions

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.

Examples

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';

Download

Get the script from the Downloads page.

More reading

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.