Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.