--数据库清单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 uINNER JOIN sys.server_role_members m ON u.principal_id = m.member_principal_idINNER 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 uINNER JOIN sys.database_role_members m ON u.principal_id = m.member_principal_idINNER 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.typeWHEN '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.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 order by c.name,b.name
SqlServer查看数据库信息及服务器级、数据库级、数据库独立 用户权限
没有评论:
发表评论