MSSQL 데이터 베이스 권한 조회

SELECT Roles.Name
	, Roles.Type_Desc
    , Members.Name MemberName
    , Members.Type_Desc

FROM sys.server_role_members RoleMembers

INNER JOIN sys.server_principals Roles 
ON Roles.Principal_Id = RoleMembers.Role_Principal_Id

INNER JOIN sys.server_principals Members 
ON Members.Principal_Id = RoleMembers.Member_Principal_Id

where Members.name not like '%system%' 
and Members.name not like '%SQLServer%'

1. 데이터 베이스에 연결하여 로그인 권한만을 조회 쿼리문

 

 SELECT @@Servername as ServerName
 	, db_name() as DBName
    ,Roles.Name
    , Roles.Type_Desc as RDesc
    , Members.Name MemberName
    , Members.Type_Desc as MDesc

FROM sys.database_role_members RoleMembers

INNER JOIN sys.database_principals Roles 
ON Roles.Principal_Id = RoleMembers.Role_Principal_Id

INNER JOIN sys.database_principals Members 
ON Members.Principal_Id =RoleMembers.Member_Principal_Id

2. 데이터베이스에 접근 권한을 조회하는 쿼리문