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_princi --服务器级权限
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.typeWHEN ‘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.PROTECTTYPEWHEN 204 THEN ‘GRANT_W_GRANT‘WHEN 205 THEN ‘GRANT‘WHEN 206 THEN ‘DENY‘ELSE ‘OTHER‘END AS PROTECTTYPEfrom sysprotects a inner join sysobjects b on a.ID = b.IDinner join sysusers c on a.uID = c.uID

总结

以上是内存溢出为你收集整理的SQLSERVER列出所有用户权限全部内容，希望文章能够帮你解决SQLSERVER列出所有用户权限所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。