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?

  • why don't you export it into a txt file?, what OS are you using?
    – jcho360
    Commented May 16, 2012 at 13:17
  • 1
    @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
    Commented May 16, 2012 at 13:34
  • are you using SSMS?
    – jcho360
    Commented May 16, 2012 at 13:37
  • 1
    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.
    – PseudoToad
    Commented Jun 13, 2014 at 0:07

1 Answer 1


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

Reviewing SQL Server Permissions | TechRepublic http://tek.io/KfzEyp


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 = grantee.name
       ,GrantorName = grantor.name
        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

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.