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

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

 

• 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%'

 

 

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

select user_name(execute_as_principal_id) 'execute as user', * from sys.system_sql_modules where execute_as_principal_id is not null select user_name(execute_as_principal_id) 'execute as user', * from sys.service_queues where execute_as_principal_id is not null select user_name(execute_as_principal_id) 'execute as user', * from sys.assembly_modules where execute_as_principal_id is not null 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 is not null select user_name(execute_as_principal_id) 'execute as user', * from sys.all_sql_modules where execute_as_principal_id is not null

 

 

 

[참고자료]

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

 

 

2016-12-07 / 강성욱 / http://sqlmvp.kr

 

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