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 May 16 '12 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 May 16 '12 at 13:34
  • are you using SSMS? – jcho360 May 16 '12 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 Jun 13 '14 at 0:07

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, privacy policy and cookie policy

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