Shulou(Shulou.com)06/01 Report--

-- Server-level permissions

WITH CTE AS

(

SELECT u.name AS user name

Whether u.is_disabled AS is disabled

G.name as server role

'√'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 server role IN ([public])

[sysadmin]

[securityadmin]

[serveradmin]

[setupadmin]

[processadmin]

[diskadmin]

[dbcreator]

[bulkadmin]) AS T

-- Database level permissions

WITH CTE AS

(

SELECT u.name AS user name

G.name AS database role

'√'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 database role IN ([public])

[db_owner]

[db_accessadmin]

[db_securityadmin]

[db_ddladmin]

[db_backupoperator]

[db_datareader]

[db_datawriter]

[db_denydatareader]

[db_denydatawriter]) AS T

-- separate permissions at the database level

Select c.name as user name, b.name as object name

CASE b.type

WHEN'U' THEN 'Table'

WHEN'P 'THEN' Procedure'

ELSE 'OTHER'

END AS object type

CASE WHEN a.ACTION = 26 AND a.PROTECTTYPE = 205THEN '√' ELSE''END AS' REFERENCES'

CASE WHEN a.ACTION = 193 AND a.PROTECTTYPE = 205 THEN '√' ELSE''END AS' SELECT'

CASE WHEN a.ACTION = 195AND a.PROTECTTYPE = 205THEN '√' ELSE''END AS' INSERT'

CASE WHEN a.ACTION = 197AND a.PROTECTTYPE = 205THEN '√' ELSE''END AS' UPDATE'

CASE WHEN a.ACTION = 196AND a.PROTECTTYPE = 205THEN '√' ELSE''END AS' DELETE'

CASE WHEN a.ACTION = 224 AND a.PROTECTTYPE = 205 THEN '√' ELSE''END AS' EXECUTE'

CASE a.PROTECTTYPE

WHEN 204THEN 'GRANT_W_GRANT'

WHEN 205THEN 'GRANT'

WHEN 206THEN 'DENY'

ELSE 'OTHER'

END AS PROTECTTYPE

From sysprotects an inner join sysobjects b on a.id = b.id

Inner join sysusers c on a.uid = c.uid

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.