SQL Server에서 Drop 및 Delete 사용자 찾기

SQL Server에서 Drop 및 Delete 사용자 찾기

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

누군가 SQL Server에서 데이터를 삭제 했다? 그런데 누가 명령을 실행 하였는지 아무도 말을 하지 않는다? 트랜잭션 로그를 사용하여 어떤 사용자가 데이터를 삭제하였는지 알아보자.

 

이번 포스트를 시작하기 전에 지난 포스트를 참고하면 이해하는데 많은 도움이 될 듯 하다.

• 트랜잭션 로그 읽기 : http://sqlmvp.kr/140202102618

 

[Delete 문을 실행한 사용자 찾기]

데이터를 삭제한 사용자를 찾기 위해 예제 데이터베이스 및 테이블을 생성한다.

--Create DB. USE [master]; GO CREATE DATABASE ReadingDBLog; GO -- Create tables. USE ReadingDBLog; GO CREATE TABLE [Location] ( [Sr.No] INT IDENTITY, [Date] DATETIME DEFAULT GETDATE (), [City] CHAR (25) DEFAULT 'Seoul');

 

데이터를 입력 한다.

USE ReadingDBLog GO INSERT INTO Location DEFAULT VALUES ; GO 100

 

데이터를 삭제 한다. 해당 행이 삭제 된 것을 확인 할 수 있다.

USE ReadingDBLog GO DELETE Location WHERE [Sr.No]=10 GO SELECT * FROM Location WHERE [Sr.No]=10 GO

 

 

 

삭제 된 행에 대한 정보를 찾기 위해 트랜잭션 로그 파일을 검색한다. 다음 스크립트를 실행하여 삭제된 모든 트랜잭션 로그에 대한 정보를 조회 한다. AllocUnitName 열에서 테이블의 이름을 확인 할 수 있으며 Delete문이 트랜잭션 ID 0000:00003dc (사용자마다 다름)로 Heap 테이블 Location에서 발생한 것을 확인 할 수 있다.

USE ReadingDBLog GO SELECT [Transaction ID], Operation, Context, AllocUnitName   FROM fn_dblog(NULL, NULL) WHERE Operation = 'LOP_DELETE_ROWS'

 

 

 

데이터를 삭제한 사용자의 트랜잭션 SID를 확인하기 위해 트랜잭션 ID를 이용하여 다음 스크립트를 실행 한다.

USE ReadingDBLog GO SELECT Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID] FROM fn_dblog(NULL, NULL) WHERE [Transaction ID] = '0000:000003dc' AND [Operation] = 'LOP_BEGIN_XACT'

 

 

 

Delete에 대한 트랜잭션 SID열에서 16진수 값을 복사하여 다음 SUSER_SNAME() 함수에 대입하여 사용자를 확인 할 수 있다.

USE MASTER GO SELECT SUSER_SNAME(0x010500000000000515000000D901141347D21421334C1B86F4010000)

 

 

 

[Drop 문을 사용한 사용자 찾기]

[Drop 문을 사용한 사용자 찾기]는 [Delete 문을 사용한 사용자 찾기]와 거의 유사하다. 다음 실습을 통해 알아보자.

 

테이블을 삭제 한다.

USE ReadingDBLog GO DROP TABLE Location

 

 

트랜잭션 로그를 확인 한다.

USE ReadingDBLog GO SELECT Operation, [Transaction Id], [Transaction SID], [Transaction Name], [Begin Time], [SPID], Description FROM fn_dblog (NULL, NULL) WHERE [Transaction Name] = 'DROPOBJ' GO

 

 

 

SID를 사용하여 사용자를 확인 한다.

SELECT SUSER_SNAME(0x010500000000000515000000D901141347D21421334C1B86F4010000)

 

 

 

이번 실습을 통하여 데이터베이스에서 발생하는 로그 정보 조회 및 작업자를 찾는 방법에 대해서알아 보았다. 이 포스트에는 문서화 되지 않은 함수를 사용하였다. 문서화 되지 않은 함수를 사용할 때에는 반드시 테스트 서버에서 안정성을 검증하여 사용할 수 있도록 한다.

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3090/how-to-find-user-who-ran-drop-or-delete-statements-on-your-sql-server-objects/

 

 

2013-12-05 / 강성욱 / http://sqlmvp.kr