Drop Failed For User
Published: Mar 18, 2016
This was an error that I hadn’t seen in a while and therefore I thought I would mention it. The error normally seen here is around the user owning a schema and so it cannot be dropped, but this was different:

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

So what does this mean? Well it’s actually quite simple, although at first it may not seem that way.

It basically means exactly as it states… the user you are trying to drop is named explicitly in an “execute as” statement within a stored procedure etc.

The easiest way to find this and therefore rectify the problem is to run a simple piece of code.

Here’s the error I got in my test environment for an invented login of masterUser (I’m not sure why I didn’t use “testUser”… must have had a brain blip).

So how do we find the code which is causing this problem?

select object_name(object_id)
from sys.sql_modules
where execute_as_principal_id = user_id('masterUser')


The result of this was as follows:

A quick look at the proc itself shows us the problem:

create procedure myTestProc
  
with execute as 'masterUser'
as
   select
*
  
from AdventureWorks2012.sales.SalesOrderHeader
go


All we have to do now is to drop that procedure (if safe to do so) or simply change the execute as context to refer to another user and then SQL Server will allow us to drop masterUser.

Hope that helps if anyone else ever comes across this error.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron