[MSSQL] 로그인 히스토리 쿼리

MSSQL 에서 로그인 히스토리를 추적 하는 쿼리 2가지 확인이 되어 정리 해 볼까 한다.

---

1. 보안 - 감사를 통한 히스토리

 

SELECT  TOP 2000 
		Audit.EVENT_TIME
		, Audit.ACTION_ID  
		, Audit.SUCCEEDED				--  1 - TRUE(성공), 0 - FALSE (실패)
		, Audit.SERVER_PRINCIPAL_NAME 	--서버 보안 주체 이름
		, Audit.SERVER_PRINCIPAL_SID  	--서버 보안 주체 SID
		, Audit.STATEMENT 
		, Audit.FILE_NAME
FROM sys.fn_get_audit_file ('\<path>\*',default,default) Audit
		INNER JOIN (SELECT DISTINCT action_id, [name] FROM Sys.dm_audit_actions where ACTION_ID IN ('LGIS','LGIF','LGO')) AuAction ON Audit.action_id = AuAction.action_id 
WHERE  Audit.server_principal_name='계정ID'

 

 [ACTION_ID 동작 ID]

• LGIF - LOGIN FAILED
• LGFL - FAILED_LOGIN_GROUP
• LGIS - LOGIN SUCCEEDED
• LGO - LOGOUT
• LGSD - SUCCESSFUL_LOGIN_GROUP
• LO - LOGOUT_GROUP

2. 추적파일을 통한 히스토리 

 

1) 기본 추적기능
SELECT	* 
FROM	sys.configurations 
WHERE	name = 'default trace enabled';


2) 기본 추적파일의 속성
SELECT	* 
FROM	sys.traces

3) 프로시저 추적 파일 조회

SELECT TOP 10	A.*
		, B.*
FROM	fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1 F.[value] FROM sys.fn_trace_getinfo(NULL) F WHERE  F.property = 2  )), DEFAULT) as A
	inner join sys.trace_events as B on A.EventClass = B.trace_event_id
	inner join sys.trace_categories as c on b.category_id = c.category_id
WHERE B.trace_event_id IN (14,15,20)
	AND A.LoginName NOT IN ('NT AUTHORITY\NETWORK SERVICE') 
	AND A.StartTime > = '2023-05-07'
	AND A.HOSTNAME='HOSTNAME'
ORDER BY A.STARTTIME DESC

--[sys.trace_event_id 테이블 ]
-- 14 - Audit Login
-- 15 - Audit Logout
-- 20 - Audit Login Failed

[2]
SELECT 	A.*
FROM	fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1 F.[value] FROM sys.fn_trace_getinfo(NULL) F WHERE  F.property = 2  )), DEFAULT) as A
WHERE  A.eventclass IN (20,14,15,118,131,114)
	AND A.sessionloginname NOT IN('NT AUTHORITY\SYSTEM')
	AND A.databasename NOT IN ('mssqlsystemresource') (login -> null / logout -> null x)
	AND A.APPLICATIONNAME NOT IN ('.Net SqlClient Data Provider','Microsoft SQL Server Management Studio - Transact-SQL IntelliSense')
	AND A.SESSIONLOGINNAME='SESSIONLOGINNAME'
ORDER BY A.STARTTIME DESC

 

[참고]

[이벤트 정보 조회 쿼리]
SELECT	distinct c.name as category_name
		, a.eventid, b.name as event_name
FROM	fn_trace_geteventinfo(1) as a
	inner join sys.trace_events as b on a.eventid = b.trace_event_id
	inner join sys.trace_categories as c on b.category_id = c.category_id
    
    
EXEC sp_readerrorlog 0,1,'login'

## 사용자 관련 명령어
- user : db 사용자 이름
- username(1) - db 사용자를 순차적으로 반환
- curent_name - 현재 로그인한 db 사용자 이름
- sessin_user - 현재 세션의 db 사용자 이름
- sp_helpuser - 모든 사용자 정보에 대해서 확인
- sys.syslogins - 모든 사용자의 로그인한 히스토리 정보
- sp_helplogins - 로그인 정보에 대해서 확인
- sts.sql_logins - sql 로그인 정보 . id, 패스워드 해쉬값
- xp_logininfo - 사용자 로그인에 대한 정보
- has_dbaccess - 사용자가 db에 접근 권한이 있는지 확인