SQLSERVER lists all user permissions
-- 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
