SQL Server用户权限查询

--服务器级权限
WITH CTE AS (
    SELECT
        u.name AS 用户名,
        u.is_disabled AS 是否禁用,
        g.name AS 服务器角色,
        '√' AS 'flag' 
    FROM
        sys.server_principals u
        INNER JOIN sys.server_role_members m ON u.principal_id = m.member_principal_id
        INNER JOIN sys.server_principals g ON g.principal_id = m.role_principal_id 
    ) SELECT
    * 
FROM
    CTE PIVOT ( MAX ( flag ) FOR 服务器角色 IN ( [public], [sysadmin], [securityadmin], [serveradmin], [setupadmin], [processadmin], [diskadmin], [dbcreator], [bulkadmin] ) ) AS T;

--数据库级权限
WITH CTE AS (
    SELECT
        u.name AS 用户名,
        g.name AS 数据库角色,
        '√' AS 'flag' 
    FROM
        sys.database_principals u
        INNER JOIN sys.database_role_members m ON u.principal_id = m.member_principal_id
        INNER JOIN sys.database_principals g ON g.principal_id = m.role_principal_id 
    ) SELECT
    * 
FROM
    CTE PIVOT (
    MAX ( flag ) FOR 数据库角色 IN ( [public], [db_owner], [db_accessadmin], [db_securityadmin], [db_ddladmin], [db_backupoperator], [db_datareader], [db_datawriter], [db_denydatareader], [db_denydatawriter] ) 
    ) AS T;

--数据库级单独权限
SELECT
    c.name AS 用户名,
    b.name AS 对象名,
CASE
        b.type 
        WHEN 'U' THEN
        'Table' 
        WHEN 'P' THEN
        'Procedure' ELSE 'OTHER' 
    END AS 对象类型,
CASE
        
        WHEN a.ACTION = 26 
        AND a.PROTECTTYPE = 205 THEN
            '√' ELSE '' 
            END AS 'REFERENCES',
    CASE
            
            WHEN a.ACTION = 193 
            AND a.PROTECTTYPE = 205 THEN
                '√' ELSE '' 
                END AS 'SELECT',
        CASE
                
                WHEN a.ACTION = 195 
                AND a.PROTECTTYPE = 205 THEN
                    '√' ELSE '' 
                    END AS 'INSERT',
            CASE
                    
                    WHEN a.ACTION = 197 
                    AND a.PROTECTTYPE = 205 THEN
                        '√' ELSE '' 
                        END AS 'UPDATE',
                CASE
                        
                        WHEN a.ACTION = 196 
                        AND a.PROTECTTYPE = 205 THEN
                            '√' ELSE '' 
                            END AS 'DELETE',
                    CASE
                            
                            WHEN a.ACTION = 224 
                            AND a.PROTECTTYPE = 205 THEN
                                '√' ELSE '' 
                                END AS 'EXECUTE',
                        CASE
                                a.PROTECTTYPE 
                                WHEN 204 THEN
                                'GRANT_W_GRANT' 
                                WHEN 205 THEN
                                'GRANT' 
                                WHEN 206 THEN
                                'DENY' ELSE 'OTHER' 
                            END AS PROTECTTYPE 
                        FROM
                            sysprotects a
                        INNER JOIN sysobjects b ON a.id = b.id
    INNER JOIN sysusers c ON a.uid = c.uid;