How Effective Permissions Are Determined on a specific Database for a specific User (Query)

Question

I'd like to create a query to read out all effective permissions for a user on a database. That includes fixed role permissions and any other right granted to a user. Is started like this, but now I am stuck:

--The permissions of fixed database roles do not appear in sys.database_permissions. Therefore, database principals may have additional permissions not listed here.

    SELECT * FROM   
    (
        SELECT 
            perm.permission_name AS 'PERMISSION'
            ,perm.state_desc AS 'RIGHT'    
            ,perm.class_desc AS 'RIGHT_ON'
            ,p.NAME AS 'GRANTEE'
            ,m.NAME AS 'USERNAME'
            ,s.name AS 'SCHEMA'
            ,o.name AS 'OBJECT'
            ,IIF(perm.class = 0, db_name(), NULL) AS 'DATABASE'
        FROM
            sys.database_permissions perm
        INNER JOIN sys.database_principals p ON p.principal_id = perm.grantee_principal_id
        LEFT JOIN sys.database_role_members rm ON rm.role_principal_id = p.principal_id
        LEFT JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
        LEFT JOIN sys.schemas s ON perm.class = 3 AND perm.major_id = s.schema_id
        LEFT JOIN sys.objects AS o ON perm.class = 1 AND perm.major_id = o.object_id
    UNION ALL
        SELECT 
            perm.permission_name AS 'PERMISSION'
            ,perm.state_desc AS 'RIGHT'
            ,perm.class_desc AS 'RIGHT_ON'
            ,'SELF-GRANTED' AS 'GRANTEE'
            ,p.NAME AS 'USERNAME'
            ,s.name AS 'SCHEMA'
            ,o.name AS 'OBJECT'
            ,IIF(perm.class = 0, db_name(), NULL) AS 'DATABASE'
        FROM
            sys.database_permissions perm
        INNER JOIN sys.database_principals p ON p.principal_id = perm.grantee_principal_id
        LEFT JOIN sys.schemas s ON perm.class = 3 AND perm.major_id = s.schema_id
        LEFT JOIN sys.objects AS o ON perm.class = 1 AND perm.major_id = o.object_id
    ) AS [union]
        WHERE [union].USERNAME = 'Username'
        ORDER BY [union].GRANTEE,[union].RIGHT_ON, [union].PERMISSION

Any Ideas ?

Its already done in this [link](http://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database) — SubqueryCrunch, Oct 16 '14 at 13:39
not really.... There are missing permissions like them which were granted from a sysadmin role. Thats just only server principals granted directly to a login. The permissions coming from any fixed server role are still missing. — Pwnstar, Oct 17 '14 at 12:08

score 0 · Answer 1 · answered Oct 17 '14 at 12:09

Still missing fixed server role permissions

    SELECT * FROM   
    (
    SELECT 
    perm.permission_name AS 'PERMISSION'
    ,perm.state_desc AS 'RIGHT'
    ,perm.class_desc AS 'RIGHT_ON'
    ,p.NAME AS 'GRANTEE'
    ,m.NAME AS 'USERNAME'
    ,s.name AS 'SCHEMA'
    ,o.name AS 'OBJECT'
    ,IIF(perm.class = 0, db_name(), NULL) AS 'DATABASE'
    FROM
    sys.database_permissions perm
    INNER JOIN sys.database_principals p ON p.principal_id = perm.grantee_principal_id
    LEFT JOIN sys.database_role_members rm ON rm.role_principal_id = p.principal_id
    LEFT JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
    LEFT JOIN sys.schemas s ON perm.class = 3 AND perm.major_id = s.schema_id
    LEFT JOIN sys.objects AS o ON perm.class = 1 AND perm.major_id = o.object_id
    UNION ALL
    SELECT 
    perm.permission_name AS 'PERMISSION'
    ,perm.state_desc AS 'RIGHT'
    ,perm.class_desc AS 'RIGHT_ON'
    ,NULL AS 'GRANTEE'
    ,p.NAME AS 'USERNAME'
    ,s.name AS 'SCHEMA'
    ,o.name AS 'OBJECT'
    ,IIF(perm.class = 0, db_name(), NULL) AS 'DATABASE'
    FROM
    sys.database_permissions perm
    INNER JOIN sys.database_principals p ON p.principal_id = perm.grantee_principal_id
    LEFT JOIN sys.schemas s ON perm.class = 3 AND perm.major_id = s.schema_id
    LEFT JOIN sys.objects AS o ON perm.class = 1 AND perm.major_id = o.object_id
    UNION ALL
    SELECT 
    perm.permission_name AS 'PERMISSION'
    ,perm.state_desc AS 'RIGHT' 
    ,perm.class_desc AS 'RIGHT_ON' 
    ,roleprinc.[name] AS 'GRANTEE'
    ,'All' AS 'USERNAME'      
    ,s.name AS 'SCHEMA'
    ,OBJECT_NAME(perm.major_id) AS 'OBJECT'
    ,IIF(perm.class = 0, db_name(), NULL) AS 'DATABASE'
    FROM sys.database_principals roleprinc
    LEFT JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]  
    LEFT JOIN sys.schemas s ON perm.class = 3 AND perm.major_id = s.schema_id
    LEFT JOIN sys.objects AS o ON perm.class = 1 AND perm.major_id = o.object_id
    WHERE roleprinc.[type] = 'R' 
    AND roleprinc.[name] = 'public'
    UNION ALL
    SELECT 
    perm.permission_name AS 'PERMISSION'
    ,perm.state_desc AS 'RIGHT'
    ,perm.class_desc AS 'RIGHT_ON'
    ,p.TYPE_DESC collate DATABASE_DEFAULT AS 'GRANTEE'
    ,p.NAME AS 'USERNAME'
    ,null AS 'SCHEMA'
    ,null AS 'OBJECT'
    ,null AS 'DATABASE'
    FROM
    sys.server_permissions perm 
    INNER JOIN sys.server_principals p ON p.principal_id = perm.grantee_principal_id
    LEFT JOIN sys.server_role_members rm ON rm.role_principal_id = p.principal_id
    LEFT JOIN sys.server_principals m ON rm.member_principal_id = m.principal_id
    UNION ALL
    select  
    NULL AS 'PERMISSION'
    ,NULL AS 'RIGHT'
    ,'SERVER' AS 'RIGHT_ON'
    ,SUSER_NAME(rm.role_principal_id) AS 'GRANTEE'
    ,lgn.name AS 'USERNAME'
    ,null AS 'SCHEMA'
    ,null AS 'OBJECT'
    ,null AS 'DATABASE'
    FROM
    sys.server_principals r 
    INNER JOIN sys.server_role_members rm ON r.principal_id = rm.role_principal_id
    INNER JOIN sys.server_principals lgn ON rm.member_principal_id = lgn.principal_id
    ) AS [union]
    WHERE [union].USERNAME = 'ALL'
    OR [union].USERNAME = 'Username' -- Insert Username/login Name here (have to be username = loginname)
    ORDER BY [union].GRANTEE,[union].RIGHT_ON, [union].PERMISSION

How Effective Permissions Are Determined on a specific Database for a specific User (Query)

1 Answers1