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

 

When attempting to drop a database user, you get the following message:

 

One place to begin is your stored procedures.  The database user has likley been used in the “WITH EXECUTE AS” option in a stored procedure:

Hey, didja know that RedGate SQL-Search is FREE! Hint! Hint! Go get it. It will be a BIG help in this case.

Advertisements

About combatdba

I'm a production DBA at a terabyte-class SQL Server Shop
This entry was posted in Procedures. Bookmark the permalink.

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

  1. Phil says:

    This post was exceptionally helpful in 3 different ways. It showed me that there is an option to run a procedure or function as another user, that Red Gate had another ridiculously useful tool I could get, and I’ve got a solution for a problem that may pop up in future. Thanks!

  2. combatdba says:

    There are limitations on the account that you can use. It cannot be a builtin account. “sa” is right out. Also, SERVER\Administrators, or SERVER\Users cannot be used because domain groups are a no-no. Can’t use anything like the NTAuthority system account. It must be a single user ID. And that user ID must be granted sufficient privs in the target database to accomplish whatever it is you have purposed it to do. Otherwise, you will find yourself staring down the dreaded “user so-n-so cannot access database ‘xyz’ under the current security context” (which makes me want to pull all my fingernails out whenever I see it).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s