데이터베이스에서 사용자 삭제 오류

 

  • Version : SQL Server

 

SQL Server에서 사용자를 삭제할때 삭제하려는 사용자 계정이 특정 개체를 소유하거나 사용 권한을 가지고 있을때 다음과 같은 오류메시지와 함께 작업이 실패한다.

Msg 15136, Level 16, State 1, Line 2

The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped.

 

이런 경우 특정 사용자 계정이 어떤 개체를 소유하거나 권한을 가지고 있는지 확인하여 해당 권한을 삭제한 후 계정을 삭제해야 한다.

 

SQL Server에는 다양한 시스템 뷰가 있으며execute_as_principal_id 컬럼은 EXECITE AS 데이터베이스 보안주체 ID를 나타낸다. 아래 스크립트는 시스템뷰에서 execute_as_principal_id를 사용하는 모든 뷰의 목록을 확인할 수 있다.

select object_name(object_id) 'view name' from sys.system_views

where object_definition (object_id) like '%execute_as_principal_id%'

 

231B184F584852E30675FB

 

위의 뷰 목록을 기반으로 아래 스크립트를 실행하여 특정 계정이 사용하는 뷰 목록을 확인할 수 있다.

select user_name(execute_as_principal_id) 'execute as user', * from sys.system_sql_modules where execute_as_principal_id isnot null

select user_name(execute_as_principal_id) 'execute as user', * from sys.service_queues where execute_as_principal_id is notnull

select user_name(execute_as_principal_id) 'execute as user', * from sys.assembly_modules where execute_as_principal_id is notnull

select user_name(execute_as_principal_id) 'execute as user', * from sys.sql_modules where execute_as_principal_id is not null

select user_name(execute_as_principal_id) 'execute as user', * from sys.server_assembly_modules where execute_as_principal_id is not null

select user_name(execute_as_principal_id) 'execute as user', * from sys.server_sql_modules where execute_as_principal_id isnot null

select user_name(execute_as_principal_id) 'execute as user', * from sys.all_sql_modules where execute_as_principal_id is notnull

 

 

 

[참고자료]

https://blogs.msdn.microsoft.com/psssql/2016/11/15/unable-to-drop-a-user-in-a-database/

https://msdn.microsoft.com/ko-kr/library/ms174365.aspx

 


 

SQL Server, MS SQL, 사용자 삭제, 시스템 뷰, principal, execute_as_principal_id, SQL Tip, DBA, 카탈로그 뷰



강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp





profile

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

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.