MSSQL資料庫權限查詢

資料庫中權限查詢 帳號與資料庫角色的從屬關係

select DB_NAME() as [DB_NAME]
, sysu.name as [登入名稱]
,case
when sysu.issqlrole =1 and sysu.islogin =0 then 'DB Role'
when sysu.issqlrole =0 and sysu.islogin =1 then 'DB Account'
else 'unKnow' End
as [AccountType]
, isnull(sysu1.name,'') as [角色名稱]
from dbo.sysusers sysu
left outer join dbo.sysmembers sysm on sysu.uid = sysm.memberuid
left outer join dbo.sysusers sysu1 on sysm.groupuid = sysu1.uid
where sysu.issqlrole = 0 and sysu.hasdbaccess = 1


帳號對結構描述的權限

SELECT DB_NAME() as [DB_NAME]
, USER_NAME(grantee_principal_id) as [登入名稱]
,case
when Prin.type ='R' then 'DB Role'
when Prin.type ='S' then 'DB Account'
else 'unKnow' End
as [AccountType]
, SCHEMA_NAME(major_id) as [SCHEMA_NAME]
, permission_name as [permission]
FROM sys.database_permissions AS Perm
JOIN sys.database_principals AS Prin
ON Perm.major_ID = Prin.principal_id AND class_desc = 'SCHEMA'


物件的權限清單

select DB_NAME() as [DB_NAME], U.name as username
,case
when U.issqlrole =1 and U.islogin =0 then 'DB Role'
when U.issqlrole =0 and U.islogin =1 then 'DB Account'
else 'unKnow' End
as [AccountType]
, CASE perm.[class]
WHEN 1 THEN obj.type_desc -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END as ObjectType
,O.name as object /* , ISNULL(col.[name],'') as ColumnName*/
, permission_name as permission
from sys.database_permissions perm
LEFT JOIN sys.columns col ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
join sys.sysusers U on perm.grantee_principal_id = uid
join sys.sysobjects O on perm.major_id = id order by U.name