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

  • Z时代
  • 2024-01-10
  • 分类：综合

database

sqlserver数据库信息、数据库权限

--数据库清单
SELECT*FROM Master..SysDatabases ORDERBY 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
INNERJOIN sys.server_role_members m ON u.principal_id = m.member_principal_id
INNERJOIN 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
INNERJOIN sys.database_role_members m ON u.principal_id = m.member_principal_id
INNERJOIN 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"
ENDAS ObjectType,
CASEWHEN a.ACTION =26AND a.PROTECTTYPE =205THEN"√"ELSE""ENDAS"REFERENCES",
CASEWHEN a.ACTION =193AND a.PROTECTTYPE =205THEN"√"ELSE""ENDAS"SELECT",
CASEWHEN a.ACTION =195AND a.PROTECTTYPE =205THEN"√"ELSE""ENDAS"INSERT",
CASEWHEN a.ACTION =197AND a.PROTECTTYPE =205THEN"√"ELSE""ENDAS"UPDATE",
CASEWHEN a.ACTION =196AND a.PROTECTTYPE =205THEN"√"ELSE""ENDAS"DELETE",
CASEWHEN a.ACTION =224AND a.PROTECTTYPE =205THEN"√"ELSE""ENDAS"EXECUTE",
CASE a.PROTECTTYPE
WHEN204THEN"GRANT_W_GRANT"
WHEN205THEN"GRANT"
WHEN206THEN"DENY"
ELSE"OTHER"
ENDAS ProtectType
from sysprotects a innerjoin sysobjects b on a.id = b.id
innerjoin sysusers c on a.uid = c.uid orderby c.name,b.name

 

以上是 SqlServer查看数据库信息及服务器级、数据库级、数据库独立用户权限 的全部内容， 来源链接： utcz.com/z/534193.html

回到顶部