Sign up ×
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.

I know that I can query effective permissions by using sys.fn_my_permissions:

USE myDatabase;
SELECT * FROM fn_my_permissions('dbo.myTable', 'OBJECT') 

 entity_name | subentity_name | permission_name 
 dbo.myTable |                | SELECT          
 dbo.myTable |                | UPDATE          

This tells me whether the current user has SELECT, INSERT, UPDATE, etc. permissions on myTable in database myDatabase.

Is it possible to easily find out why the user has these permissions? For example, I'd love to have a function fn_my_permissions_ex which outputs an additional reason column:

USE myDatabase;
SELECT * FROM fn_my_permissions_ex('dbo.myTable', 'OBJECT') 

 entity_name | subentity_name | permission_name | reason
 dbo.myTable |                | SELECT          | granted to database role public
 dbo.myTable |                | UPDATE          | member of group MYDOMAIN\Superusers, which belongs to database role db_datawriter

Unfortunately, I could not find such a function in the SQL Server documentation. Is there a tool or script that provides this functionality?

share|improve this question
why don't you export it into a txt file?, what OS are you using? – jcho360 May 16 '12 at 13:17
@jcho360: There is no function fn_my_permission_ex, so there's nothing that I could export. I'm asking if there is a function or tool with that functionality. – Heinzi May 16 '12 at 13:34
are you using SSMS? – jcho360 May 16 '12 at 13:37
Awesome question. I've been trying to figure this out for a very very long time. fn_my_permissions kinda works but often shows permissions that I cannot track down to an origin. – Gizmo Jun 13 '14 at 0:07

1 Answer 1

up vote 5 down vote accepted

You can find some good information regarding security from the article below.

Reviewing SQL Server Permissions | TechRepublic


The following query uses the sys.database_permissions system view to indicate which users had specific permissions inside the current database.

       ,ObjectName = OBJECT_NAME(major_id)
       ,GranteeName =
       ,GrantorName =
        sys.database_permissions dp
        JOIN sys.database_principals grantee
        ON dp.grantee_principal_id = grantee.principal_id
        JOIN sys.database_principals grantor
        ON dp.grantor_principal_id = grantor.principal_id
share|improve this answer

Your Answer


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.