Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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...

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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:

SELECT  
        T.name token_name,
        T.type token_type,
        T.usage,
        T.is_login_token,
        UDP.name user_name,
        UDP.type_desc user_type_desc,
        UDP.default_schema_name,
        UDP.create_date,
        UDP.is_fixed_role,
        --UDP.authentication_type_desc,
        SP.name login_name,
        SP.type_desc login_type,
        SP.create_date,
        --SP.is_fixed_role,
        DP.class_desc,
        DP.permission_name,
        DP.state_desc,
        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;
share|improve this answer
    
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 '13 at 15:14
    
You could accept the answer instead of upvoting it. You have enough rep for that ;) –  Sebastian Meine Nov 14 '13 at 17:43
    
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 Nov 14 '13 at 19:46
    
@Kin, thanks for noting. I commented out the 2012 only columns. –  Sebastian Meine Nov 14 '13 at 20:31
    
@SebastianMeine Good work though. +1 from me. –  Kin Nov 14 '13 at 20:33
show 1 more comment

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

http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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