What are my own effective permissions? Get Answers with sys.fn_my_permissions.

Introduction

sys.database_permissions and sys.server_permissions can be used to determine if a user has a given permission on a specific object. Hover, this is not enough to actually see if that user or login has access to the object in question. The principal could be a member of a role or even a Windows group and could get additional grants or denies through that role or group membership. You also need to take the hierarchy of securables and the hierarchy of permissions into account to see if a specific permission is in effect. In short, it is a lot of work to get from one of the permission catalog views mentioned above to the list of actually effective permissions for a security principal.

If you are just interested in the permissions of the current connection, you can greatly reduce that work by using the built-in sys.fn_my_permissions function.

Seeing all effective permissions

To see sys.fm_my_permissions in action, we need to first create a security principal and a securable:

This creates a login and an associated user that is also member in a new database role. The script also creates a schema and a table within that schema. Now we can grant a few permissions:

Note, that not everything is granting to the user. Two of the permissions go to the role and one is even a DENY. Now let us see what sys.fn_my_permissions returns, when executed by TestLogin1. For that, we are going to use this query:

The function takes two parameters that are not optional. The first takes the name of the securable you are interested in and the second represents the type of that securable. If you pass in NULL or DEFAULT for the two parameters, only server level permissions will be returned. The above query returns this result:

As you can see, if the securable is a table (or view), the result includes column level permission as well. Let us quickly walk through the result. We had granted UPDATE on the table. Therefore, we expect to see three rows returned, one telling us we can update the table and one each for the two columns. We also granted INSERT on the schema and as such should see the same three rows. However, there is only one INSERT row. The reason is, that INSERT is not a privilege that can be granted on a column. An insert is always an action that creates an entire row and therefore affects all columns.

Finally, there is a GRANT SELECT on the schema and a DENY SELECT on one of the columns. That means that effectively only the id column can be selected from. Therefore above result contains only one row pertaining to the SELECT privilege.

This result shows that sys.fn_my_permissions takes the hierarchy of securables and the hierarchy of security principals into account when determining the effective permissions for the current connection. The following image shows the result of running the same query, after CONTROLL on the schema TestSchema1 has been granted to the role TestRole1:

This clearly demonstrates that the hierarchy of permissions is also taken into account by sys.fn_my_permissions when working out the list of effective permissions.

Summary

The sys.fn_my_permissions function can be a helpful tool in your permission management tool box. However, it returns results only for a single securable and only for the current connection. To see the effective permissions of another security principal EXECUTE AS can be used as shown in the above examples. However, this makes this function at best a cumbersome tool to get an overview over the effective permissions on an entire database or even SQL Server instance.