[MSSQL] 22. DB 로그인 Login 계정 조회 쿼리

DB Instance에 존재하는 로그인(Login) 계정을 조회할 때 사용하는 쿼리입니다.

 

SSMS 상에서 로그인 폴더로 확인하는 방법도 있지만, 권한을 확인하기위해선 일일히 열어봐야하는 번거로움이 있습니다.

아래 쿼리를 사용하여 각 로그인에 대한 권한(role)까지 한눈에 파악이 가능합니다.

IF OBJECT_ID(N'tempdb..#LOGIN') IS NOT NULL 
BEGIN
     DROP TABLE #LOGIN;
END

CREATE TABLE #LOGIN (
     login_name SYSNAME
	,role_name SYSNAME
);

INSERT INTO #LOGIN
SELECT 
	 SL.name AS login_name
	,SR.name AS role_name
FROM master.sys.server_role_members SRM
INNER JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
    JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
AND SL.name NOT LIKE '##%##'
AND SL.name NOT LIKE 'NT AUTHORITY%'
AND SL.name NOT LIKE 'NT SERVICE%'
--AND SL.name <> ('sa')
AND SL.name <> 'distributor_admin';

SELECT 
	 SP.name AS [Login]
	,SP.type_desc AS [Login_Type]
	,SP.is_disabled AS [Disabled]
	,SP.default_database_name AS [DefaultDB]
	,SR.role_name AS [Role]
FROM     sys.server_principals AS SP
LEFT OUTER JOIN #LOGIN AS SR ON SP.name = SR.login_name
WHERE SP.type IN ('S','G','U')
AND SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE 'NT AUTHORITY%'
AND SP.name NOT LIKE 'NT SERVICE%'
AND SP.name <> ('sa')
AND SP.name <> 'distributor_admin'
ORDER BY SP.name;