SqlServer查看数据库信息及服务器级、数据库级、数据库独立用户权限

小小码 • 2023-03-12 18:00 • 代码基础 • 阅读 161

大家好，我是考100分的小小码，祝大家学习进步，加薪顺利呀。今天说一说SqlServer查看数据库信息及服务器级、数据库级、数据库独立用户权限,希望您对编程的造诣更进一步.

SqlServer查看数据库信息及服务器级、数据库级、数据库独立用户权限

--数据库清单
SELECT * FROM Master..SysDatabases ORDER BY Name;  

--服务器级用户权限
WITH CTE AS
(
SELECT u.name AS UserName,
u.is_disabled AS IsDisabled,
g.name as svrRole,
"√" 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 svrRole IN ([public],
[sysadmin],
[securityadmin],
[serveradmin],
[setupadmin],
[processadmin],
[diskadmin],
[dbcreator],
[bulkadmin])) as rg;

--数据库级用户权限
WITH CTE AS
(
SELECT u.name AS UserName,
g.name AS dbRole,
"√" 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 dbRole IN ([public],
[db_owner],
[db_accessadmin],
[db_securityadmin],
[db_ddladmin],
[db_backupoperator],
[db_datareader],
[db_datawriter],
[db_denydatareader],
[db_denydatawriter])) as rg;

--数据库级独立用户权限
select c.name as UserName,b.name as ObjectName,
CASE b.type
WHEN "U" THEN "Table"
WHEN "P" THEN "Procedure"
ELSE "OTHER"
END AS ObjectType,
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 order by c.name,b.name

代码100分

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/7851.html

SqlServer查看数据库信息及服务器级、数据库级、数据库独立 用户权限

相关推荐

发表回复

SqlServer查看数据库信息及服务器级、数据库级、数据库独立用户权限