Is there any way in which I can see what permissions are being used to determine a users access to a given object? I'm able to see which permissions have been granted to various roles and users within a database, but am having problems with these reports not lining up with what is actually happening.

For example, the SQL below will return my effective permissions on a given object:

SELECT * FROM fn_my_permissions('dbo.SomeTable', 'OBJECT') 
ORDER BY subentity_name, permission_name ; 

Which returns:

entity_name         subentity_name permission_name
------------------- -------------- ---------------
dbo.SomeTable                      INSERT
dbo.SomeTable                      SELECT
dbo.SomeTable                      UPDATE

What would be very useful is something along the lines of below, which explains where each line item is coming from:

entity_name         subentity_name permission_name reason
------------------- -------------- --------------- ----------------
dbo.SomeTable                      INSERT          dbowner
dbo.SomeTable                      SELECT          role: datareader
dbo.SomeTable                      UPDATE          role: sysadmin

Note, there is a similar question on the site, however the accepted answer is a query that returns the permissions that are set, not where said permissions are coming from.

Edit: The reason for asking was that there were 'shadows' of former permissions as seen and explained here. However if I had access to something like the above, it would have shown the SQL Server thought I was a member of a given role and thus pointed me in the right direction...

2 Answers 2


Have a look at sys.user_token (http://technet.microsoft.com/en-us/library/ms188421.aspx) and sys.login_token (http://technet.microsoft.com/en-us/library/ms186740.aspx). They tell you which roles/logins/users SQL Server is involving when figuring out the permissions for the current user.

Then you can use sys.database_permissions (http://msdn.microsoft.com/en-us/library/ms188367.aspx) and sys.server_permissions (http://msdn.microsoft.com/en-us/library/ms186260.aspx) to get the permissions associated with each token. (They even tell you who granted the permission.)

Finally, sys.database_principals (http://msdn.microsoft.com/en-us/library/ms187328.aspx) and sys.server_principals (http://msdn.microsoft.com/en-us/library/ms188786.aspx) can be used to translate the principal_id into a name.

I took a stab at combining the system views above. It might not be perfect, but it should get you started:

        T.name token_name,
        T.type token_type,
        UDP.name user_name,
        UDP.type_desc user_type_desc,
        SP.name login_name,
        SP.type_desc login_type,
        QUOTENAME(OBJECT_SCHEMA_NAME(O.object_id))+'.'+QUOTENAME(O.name) object_name,
        O.type_desc object_type_desc,
        C.name column_name
  FROM (SELECT 0 is_login_token,* FROM sys.user_token 
        UNION ALL
        SELECT 1 is_login_token,* FROM sys.login_token
       )AS T
  LEFT JOIN sys.database_principals AS UDP
    ON T.principal_id = UDP.principal_id
   AND T.is_login_token = 0
  LEFT JOIN sys.server_principals AS SP
    ON (UDP.sid = SP.sid AND T.is_login_token = 0)
    OR (T.principal_id = SP.principal_id AND T.is_login_token = 1)
  LEFT JOIN sys.database_permissions AS DP
    ON (DP.grantee_principal_id = UDP.principal_id
        AND T.is_login_token = 0)
    OR (DP.grantee_principal_id = SP.principal_id
        AND T.is_login_token = 1)
  LEFT JOIN sys.all_objects AS O
    ON DP.major_id = O.object_id
  LEFT JOIN sys.all_columns AS C
    ON DP.major_id = C.object_id
   AND DP.minor_id = C.column_id;
  • Ah, I've not come across the sys._token tables. They look quite handy. I guess they can be used, along with the 'constants' such as the fixed roles and the db owner to create an appropriate query. (I'd upvote you, but I dont have enough rep...)
    – BJury
    Nov 14, 2013 at 15:14
  • UDP.authentication_type_desc is not available in sql 2008 or 2008R2 for sys.database_principals. Also, it should be UDP.is_fixed_role.
    – Kin Shah
    Nov 14, 2013 at 19:46
  • @Kin, thanks for noting. I commented out the 2012 only columns. Nov 14, 2013 at 20:31
  • @SebastianMeine Good work though. +1 from me.
    – Kin Shah
    Nov 14, 2013 at 20:33
  • Nice one, I didn't have a lot of time to put it together last night. Thanks! For completion we need to addin the fixed roles (dbreader, etc) and the special cases like the db owner. But thanks!
    – BJury
    Nov 15, 2013 at 9:16

Not sure if this should be an answer or a comment. But I've used the script here before for this type of question -


The last query Jamie uses shows the permission and if it is through a role, the role is specified. If it isn't, there is no role specified. This was written for SQL Server 2005, I've used it on SQL Server 2008 without issues.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.