데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

 

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

 

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

 

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

 

[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/

 

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1911 Memory Pressure jevida(강성욱) 2016.10.12 1123
1910 64비트 버전의 SQL Server 버퍼 풀 메모리 페이지 수 줄이는 방법 jevida(강성욱) 2016.10.12 2472
1909 누락된 공유 잠금 (Missing Shared Locks) jevida(강성욱) 2016.10.12 1565
1908 Ad Hoc Distributed Queries 옵션 jevida(강성욱) 2016.10.12 3104
1907 높은 MAXDOP은 쿼리를 느리게 만들 수 있는가? jevida(강성욱) 2016.10.12 1308
1906 변경된 테이블 이름 복구하기 jevida(강성욱) 2016.10.12 1308
1905 Sys,dm_exec_connections jevida(강성욱) 2016.10.12 1935
1904 SQL Server 시작 매개 변수 설정 jevida(강성욱) 2016.10.12 2492
1903 AFTER 트리거를 INSTEAD OF 트리거로 변경 후 효율성 향상 jevida(강성욱) 2016.10.12 1790
1902 쿼리 대기 옵션 jevida(강성욱) 2016.10.12 1575
1901 Index create memory 설정 jevida(강성욱) 2016.10.12 1236
1900 Min memory per query 옵션 jevida(강성욱) 2016.10.12 1690
1899 SQL Server 에디션 다운그레이드와 제한된 기능 확인 jevida(강성욱) 2016.10.12 2124
1898 Downgrade from SQL Server Ent to Std Edition jevida(강성욱) 2016.10.12 1344
1897 트랜잭션 로그 여유 공간 모니터링 jevida(강성욱) 2016.10.12 1356
1896 SQL Server가 서비스 격리를 처리하는 방법 jevida(강성욱) 2016.10.12 1145
1895 쿼리 매개변수화 확인하기 jevida(강성욱) 2016.10.12 1435
» SQL Server에서 Drop 및 Delete 사용자 찾기 jevida(강성욱) 2016.10.12 1630
1893 SQL Server 트랜잭션 로그 읽기 jevida(강성욱) 2016.10.12 2098
1892 MaxBCPThreads에 따른 BCP 병렬출력 jevida(강성욱) 2016.10.11 1090





XE Login