작성자 : 이승연

gamebible@naver.com

글 등록자 변경으로 인하여 다시 등록합니다.

다시 등록하면서 EXECUTE AS 테스트 스크립트도 추가해 보았습니다.

오랜 시간 동안 관리를 하지 못한 DB SERVER에 접속을 하여 데이터베이스 객체(TABLE, Procedure, View등)들을 정리 하고 사용자 계정도 정리 및 삭제를 하던 도중에 계정 삭제가 되지 않고 아래와 같은 에러메시지를 보게 되었습니다.

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

'

위 스크립트는 DBMS의 모든 Database 내의 Login User가 어는 권한으로 어는 객체를 사용하고 있는지 보여주는 간단한 스크립트 입니다. 해서 위 스크립트도 돌려 보았지만 해당 계정은 어떠한 권한도 없었습니다. 슬슬 짜증도 나고 궁금하기도 하고...

하지만, 왜 삭제가 안되는지는 Error메시지에 있었다는 -0-;; "프로시져, 함수 또는 이벤트 알림의 실행 컨텍스트로 설정되어 있으며 삭제할 수 없습니다." 제길 무식한게 죄다 -0-;; 실행 컨텍스트란? 이곳에 자세한  설명이 있으니 궁금하신 분은 클릭 ~~ !!

아무튼 그럼 execute as라는 구문이 도대체 어느 procedure에 있는겨 -0-;; 이거 찾는거 또한 난감합니다. 이래서 문서화를 잘 해야 하나 봅니다. 1~2시간 SQL Server System View를 찾다가 발견했습니다. 역시 무식하면 손발이 고생하나 봅니다. ㅜ.ㅜ;;;

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

해당 스크립트는 어떤 procedure에 execute as가 어떤 계정으로 걸려 있는지 볼 수 있는 스크립트입니다.

SQL_Server_2008_system_views_poster.pdf SQL Server 2008 System Views Poster 입니다. 뽀대용으로 하나 출력해 보려 인쇄쇠에 가격을 문의 하니 3만 5천원

2009. 11. 27일 글 다시 등록하면서 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