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.

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
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
add comment

1 Answer

up vote 3 down vote accepted

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

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

Except:

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

SELECT
        dp.class_desc
       ,dp.permission_name
       ,dp.state_desc
       ,ObjectName = OBJECT_NAME(major_id)
       ,GranteeName = grantee.name
       ,GrantorName = grantor.name
    FROM
        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
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.