SQL Server 사용자 삭제 실패시 삭제 방법은?

메인 관리자 DBA이가 아닌 다른 DBA가 어떠한 SQL Server를 접속 후 어떠한 계정을 삭제을 했습니다. 정상적인 삭제 완료 메시지가 출력이 되었으므로 정상적으로 삭제 된 줄로만 알았던 DBA는 데이터베이스 유저를 보고 경악을 하였습니다. 방금 자기 자신이 지운 로그인계정이 데이터베이스 유저에 그대로 남아 있었던 것 입니다. 혹시나 하여 해당 유저의 권한을 다 제거후 다시 삭제를 실행시켰지만 다음과 같은 메시지만 출력 됩니다.

그 DBA는 메인 DBA에게 꾸중을 들을까봐 해당 사항을 보고하지 않고 묵인합니다. 얼마후 해당 계정으로 서비스 되는 부분에서 Error가 발견되었고 메인 DBA는 해당 계정의 존재를 파악하게 되었습니다. 메인 DBA는 해당 계정이 DBMS로그인 에는 없고 데이터베이스 유저에는 있다는 것을 파악후 매칭이 잘못되어 그런가부다 하고 매칭을 시켜줍니다.

EXEC sp_change_users_login 'Auto_Fix', 'login2', NULL, 'bbb';

그 후 메인 DBA는 삭제를 시도 하였지만, 같은 에러 메시지만 출력됩니다. 해당 계정이 무엇을 하던 계정인지 메인 DBA는 모릅니다. 난감합니다. ;;;


얼마전에 실제로 있었던 사건사고이다. 약 2시간만에 이유와 문제를 해결하였지만 아찔한 상황이었다. 이유는 간단했다. 해당 계정에 WITH EXECUTE AS 구문이 걸려 있어서 삭제가 되지 않았던 것이다. 그럼 간단하게 테스트를 해보자.

-- 테스트 계정 2개를 만든다.
CREATE LOGIN login1 WITH PASSWORD = 'aaa'
CREATE LOGIN login2 WITH PASSWORD = 'bbb'

-- 테스트 데이터베이스에 유저를 만든다.
USE [TESTDB]
GO
CREATE USER [login1] FOR LOGIN [login1]
CREATE USER [login2] FOR LOGIN [login2]
GO

-- 테스트 데이터베이스에 테스트용 테이블을 만든다.
CREATE TABLE test (idx INT)
INSERT INTO test (idx) VALUES (1);
INSERT INTO test (idx) VALUES (2);

-- 테스트 데이터베이스에 테스트용 프로시져를 만든다.
-- 해당 SELECT 구문은 동적쿼리이기 때문에 해당 테이블에
-- SELECT 권한이 없으면 실행이 되지 않는다.
USE [TESTDB]
GO
CREATE PROCEDURE dbo.usp_test
AS
BEGIN
	EXEC('select * from test')
END
GO

-- 테스트를 위하여 login1유저에 
-- 테스트 프로시져 실행권한을 부여한다.
USE [TESTDB]
GO
GRANT EXECUTE ON [dbo].[usp_test] TO [login1]
GO

-- login1으로 DBMS에 로그인을 한 후
-- 테스트 프로시져를 실행시킨다.
EXEC dbo.usp_test

-- 에러가 발생할 것이다.
-- 에러를 수정하기 위해 login2에 SELECT 권한을 부여한 후
-- 프로시져를 수정한다.
USE [TESTDB]
GO
GRANT SELECT ON [dbo].[test] TO [login2]
GO

USE [TESTDB]
GO
ALTER PROCEDURE dbo.usp_test
WITH EXECUTE AS 'login2'
AS
BEGIN
	EXEC('select * from test')
END
GO

-- login1으로 DBMS에 로그인을 한 후
-- 테스트 프로시져를 실행시킨다.
EXEC dbo.usp_test

여기에서 아까와 같이 데이터베이스 계정 매칭이 깨지게 하려면 다음과 같은 구문을 실행시킨다.

DROP LOGIN login2

정상적으로 완료 메시지가 출력되고 DBMS 로그인계정을 보아도 삭제가 되어있지만 데이터베이스 유저에 보면 해당 계정은 살아 있다. 다음과 같은 Object 권한 스크립트에도 잡히지가 않는다.

EXEC sp_msforeachdb '
select     ''?''
,	dp.name
,	dp.type  
,	dp.type_desc
,	dp.create_date
,	dp.modify_date
,	ds.type
,	ds.permission_name
,	ds.state 
,	ds.state_desc
,	IsNull(ao.name, ''-'') as object_name
,	IsNull(ao.type, ''-'') as object_type
,	IsNull(ao.type_desc, ''-'') as object_type_desc
,	IsNull(ao.create_date, ''1900-01-01'') as object_create_date
,	IsNull(ao.modify_date, ''1900-01-01'') as object_modify_date
,	CONVERT(varchar(10), GetDate(), 120) AS RegDate
from (select * from [?].sys.database_principals where type = ''U'' or type=''S'') as dp 
			 inner join [?].sys.database_permissions as ds
			 on dp.principal_id = ds.grantee_principal_id
			 left join [?].sys.all_objects as ao
			 on ds.major_id = ao.object_id
'

해서 해당 계정이 도대체 무엇일까? 라는 고민에 1~2시간 헤매다가 찾게 되었다. WITH EXECUTE AS절이 걸리면 어떠한 뷰에 체크가 된다는것을 ~~ !!!

SELECT
	so.NAME
,	so.type_desc
,	sdp.name
,	sdp.type_desc
,	ssm.definition
FROM sys.sql_modules AS ssm INNER JOIN sys.database_principals AS sdp
ON ssm.execute_as_principal_id = sdp.principal_id INNER JOIN sys.objects AS so
ON ssm.OBJECT_ID = so.OBJECT_ID

위와 같은 스크립트를 실행시키면 WITH EXECUTE AS가 어떤 프로시져에 어떠한 계정으로 걸렸는지 확인 할수 있다.