데이터베이스 부여된 권한 조회

데이터베이스 역할의 맴버를 반환

SELECT DP1.name AS DatabaseRoleName,   
   isnull (DP2.name, 'No members') AS DatabaseUserName   
 FROM sys.database_role_members AS DRM  
 RIGHT OUTER JOIN sys.database_principals AS DP1  
   ON DRM.role_principal_id = DP1.principal_id  
 LEFT OUTER JOIN sys.database_principals AS DP2  
   ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R'
ORDER BY DP1.name;

데이터베이스 오브젝트 권한 부여 내용 조회

select DP.permission_name,
	DP.class_desc,
	DP2.name,
	object_name(major_id) AS ObjectName,
	DP1.name
from sys.database_permissions AS DP 
RIGHT OUTER JOIN sys.database_principals AS DP1 ON DP.grantee_principal_id = DP1.principal_id 
LEFT OUTER JOIN sys.database_principals AS DP2 ON DP.grantor_principal_id = DP2.principal_id
WHERE DP1.name <>'public'