RiCo技術農場

這篇列出關於權限管理上較常使用的TSQL

 

--查詢server role 相關成員
SELECT 
SSP.name AS [Login Name], 
SSP.type_desc AS [Login Type], 
UPPER(SSPS.name) AS [Server Role] 
FROM sys.server_principals SSP 
INNER JOIN sys.server_role_members SSRM 
ON SSP.principal_id=SSRM.member_principal_id 
INNER JOIN sys.server_principals SSPS 
ON SSRM.role_principal_id = SSPS.principal_id 

--查詢 db role 相關成員
SELECT 
SDP.name AS [User Name], 
SDP.type_desc AS [User Type], 
UPPER(SDPS.name) AS [Database Role] 
FROM sys.database_principals SDP 
INNER JOIN sys.database_role_members SDRM 
ON SDP.principal_id=SDRM.member_principal_id 
INNER JOIN sys.database_principals SDPS 
ON SDRM.role_principal_id = SDPS.principal_id 

--查詢目前登入帳號 SERVER Level 相關權限
select * 
from sys.fn_my_permissions('','SERVER') 

 

--查詢使用者 Database Level 相關權限
select * 
from sys.database_permissions AS perm INNER JOIN sys.database_principals AS usr 
ON perm.grantee_principal_id = usr.principal_id
WHERE usr.name='test' 
and perm.major_id = 0 
ORDER BY perm.permission_name ASC, perm.state_desc ASC 

 

--查詢使用者 Object Level 相關權限
select *
FROM 
sys.database_permissions AS perm 
INNER JOIN sys.objects AS obj 
ON perm.major_id = obj.[object_id] 
INNER JOIN sys.database_principals AS usr 
ON perm.grantee_principal_id = usr.principal_id 
LEFT JOIN sys.columns AS cl 
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id 
and usr.name='test' 
ORDER BY usr.name