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?
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'
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.