--服务器级权限 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
