What Permissions Does a Principal Have?

SQL Server Management Studio provides several ways of checking which permissions have been granted (or denied) to whom and on what objects. This is achievable on different levels within the securables hierarchy:

• Server-level permissions, per principal: on the Permissions page of the Server Properties window (accessible through the View menu when the instance node is selected in the Object Browser pane);
• Database-level permissions, per principal: on the Permissions page of the Database Properties window (accessible through the View menu when the database node is selected in the Object Browser pane);
• Object-level permissions, per principal: on the Permissions page of the Object Properties window (accessible through the View menu when an object node is selected in the Object Browser pane.

Alternatively, the Login Properties and Database User Properties windows (i.e. the Securables page) provide an overview of permissions that have been granted (or denied) to an individual Server or Database Principal, respectively. Note, that only explicit permissions are shown here, not including permissions granted/denied by default, permissions granted/denied implicitly (e.g. inferred higher in the securables hierarchy, or via the membership in built-in or user-defined server and/or database roles), etc.

In other words: viewing permissions one-object-at-a-time using SSMS *is* possible, but for a comprehensive overview of permissions granted or denied to a specific principal, T-SQL provides more powerful methods.

Beyond the GUI

SQL Server 2005 introduced an extremely useful (and long-sought after) system function that lists the permissions granted (or denied) to a given principal on a given securable. This has ever since made it quite easy for any principal to check their own permissions on individual server-level or database-level securables.

I am referring to the sys.fn_my_permissions system function.

Through impersonation (e.g. using the EXECUTE AS statement) a principal with sufficient privileges (e.g. a DBA) can check the permissions of other principals as well. In fact any principal with the IMPERSONATE permission on another principal can do so.

What? How? A permission on a principal? Whether an object is a principal or a securable, depends on the point of view, and while every principal is also a securable, not every securable is a principal. Now say that out loud seven times. You can read more about Principals, Securables, and Permissions in Books Online. The entire Securable/Permission hierarchy is also illustrated in BOL.

However, even through impersonation only a single securable at a time can be checked using sys.fn_my_permissions.

To view the permissions for several objects summarily, two additional requirements must be met:

• Be familiar with the CROSS APPLY operator; and
• Prepare a list of all relevant securables represented by their names and classes.

Constructing the list of server and database securables is not a big task, once we (a) identify which securables are relevant, (b) learn where to find information about them, and (c) determine how to classify them appropriately. As always, Books Online is your friend. And, sometimes, so am I.

I've created the query which you're very welcome to download, and I'll keep it updated as SQL Server evolves. Unless I don't, which could happen, but won't.

Securables? What Securables?

The query returns a list of all relevant securables by name and class, exactly as expected by sys.fn_my_permissions. If you disagree, please, tell me, and I'll do my best to make it more agreeable.

In order to actually see the permissions and their state we need to apply this function to the list of securables, and – ta-da! – execute it while impersonating the principal we're interested in. The permission to impersonate the target principal is assumed, of course.

You're also very welcome to download the complete script.

The Rocky Road

As has become the natural way of things on this planet lately – some limitations do apply:

• The EXECUTE AS statement expects as its sole argument a reference to a singleton principal (a login – based on a Windows user or a SQL Server Login – or a user), not a grouping principal (a role or a login based on a Windows group), so take this into account;
• This query relies on impersonation, which means that only a single principal at a time can be processed – a limitation that can be easily overcome by an appropriate client application invoking the query for each principal in question in a loop;
• The query is executed in the context of the current database which means that the information is available only for objects of the current database. This limitation can also be overcome by a client application looping through the databases one might be interested in, but bear in mind that the function cannot "see" beyond the current instance – permissions on linked servers cannot be checked;
• If the argument supplied to EXECUTE AS is a LOGIN, the query will list the permissions of the server principal; similarly if the argument is a USER, only the permissions of the database principal will be listed.

The Singleton Principal Principle

How do we check the permissions of a role? The only way I can think of is to create a singleton principal (a login and/or a user) on-the-fly, not give it any explicit permissions and make it a member of the role in question. Then execute the script in the context of this new singleton principal.

And Windows groups? Well, we can do pretty much the same, but with one additional step (create an underprivileged Windows account first – provided that the OS administrator agrees).

Again, if you have better ideas, know that to share is divine.

Default Permissions – An Ultra Quick Look

When a login (server principal) is first created (on SQL Server 2005 and later), some permissions are inferred by that very act:

1. On the server instance:
   • CONNECT SQL – what good is a login that cannot connect to the server?
   • VIEW ANY DATABASE – by default every new login can view all other databases on the server;
2. On four default endpoints¹:
   • CONNECT – obviously;
3. On logins:
   • CONTROL – by default every server principal has complete control over itself.

When a user (database principal) is created, some database-level permissions follow suit:

1. On the database:
   • CONNECT – the elementary privilege of any database principal;
2. On users:
   • CONTROL – by default every database principal has complete control over itself;
3. If diagramming has been enabled on the database, each database principal by default also receives EXECUTE permissions on modules used in creating and maintaining database diagrams (dbo.fn_diagramobjects, dbo.sp_alterdiagram, dbo.sp_creatediagram, dbo.sp_dropdiagram, dbo.sp_helpdiagramdefinition, dbo.sp_helpdiagrams and dbo.sp_renamediagram).

So, what permissions do you have? And what permissions have you given your users?

ML

¹ Four default Endpoints exist on every instance through which connections can be made to the server: TSQL Default TCP, TSQL Default VIA, TSQL Local Machine and TSQL Named Pipes. See Network Protocols and Network Libraries in Books Online for details.