Unable to drop a user in a database

A user called in for help because he wasn’t able to drop a user from a database.  The error message is below

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.

From the error, the logical starting point is sys.sql_modules because functions, stored procedures have entries in this catalog view.  Additionally, it has a column called execute_as_principal_id

 

So I looked there but found no entries whose execute_as_principal_id is the same user id of the user this customer tried to drop. Next I started to look at source code and found out this error is raised in a couple of other places.  It gave me the clue that I didn’t have to do source code research, TSQL is all I needed to solve the problem.

First I used the following to identify all the system views that have 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%'

The query produced the following:

Then I had customer run the following queries based on the output from above

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

Eventually, we discovered that customer had some CLR triggers (sys.assembly_modules ) that used the particular user in “EXECUTE AS” clause.

 

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

twitter| pssdiag |Sql Nexus

Comments

• Anonymous
  November 15, 2016
  Good Job. Thanks for your share...
  • Anonymous
    February 15, 2017
    Pretty good post. A simple issue of dropping an user can become really tricky one to diagnose.