SQL Server 使用者及物件權限列表
一、列出每個DB中各使用者的資料庫角色
EXECUTE master.sys.sp_MSforeachdb
'USE [?]; select ''?'' DBName, DBRole = g.name, MemberName = u.name
From sys.database_principals u, sys.database_principals g, sys.database_role_members m
Where g.principal_id = m.role_principal_id And u.principal_id = m.member_principal_id
Order by 1, 2, 3'
二、列出各伺服器角色裡的成員
Select ServerRole = g.name, MemberName = u.name
From sys.server_principals u, sys.server_principals g, sys.server_role_members m
Where g.principal_id = m.role_principal_id And u.principal_id = m.member_principal_id
Order By 1, 2
三、列出使用者被賦予權限的物件及權限類別
Select UserName = USERS.name, ObjectName = o.name, PermissionName = prmssn.permission_name
From sys.database_permissions prmssn
INNER JOIN sys.database_principals AS grantee
ON grantee.principal_id = prmssn.grantee_principal_id
JOIN sys.sysusers USERS ON USERS.sid=grantee.sid
JOIN sys.objects o on major_id=o.object_id
Order By 1, 2
四、列出被賦予系統擴充預儲程序權限之使用者
Select [Extended Stored Procedure] = SystemObject.name,
[Granted to] = USER_NAME(SystemPermissionObject.grantee)
From master.dbo.sysobjects As SystemObject
Inner Join master.dbo.syspermissions As SystemPermissionObject
On SystemObject.id = SystemPermissionObject.id
Where SystemObject.type = 'X'
