大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说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
