开发笔记:SQLServer用户权限查询

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