June 22, 2009 at 3:00 pm
I am unable to drop a certain DB User from a DB. I have tried to drop the DB user explicitly. I have also tried to remove the DB user from the sql login and both attempts yeild the error:
The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped. (Microsoft SQL Server, Error: 15136)
I have run the query: "select * from sys.sql_modules where execute_as_principal_id = user_id('
June 22, 2009 at 3:15 pm
Run this query:
select * from sys.event_notifications
June 23, 2009 at 7:23 am
Tried "select * from sys.event_notifications"; it returned no rows!
June 23, 2009 at 8:18 am
Is the user an owner of any schema's in the database?
June 23, 2009 at 8:23 am
Does this user own to db_owner schema. according to your text, it sounds it does...
you will have to transfer the db_owber/dbo to some other user and then the user can be deleted.
Maninder
www.dbanation.com
June 23, 2009 at 11:39 am
The DB User is not the DB owner (my AD account is) and has no schemas. I was able to remove all of its roles and it became disabled as soon as i did. I still get the error message when trying to DROP however.
July 2, 2009 at 9:16 am
Service Broker!
The database user had created some routes, services, queues, etc.
They were no longer used, deleted them and the user could be removed.
July 2, 2009 at 9:05 pm
Try user mapping and uncheck the databases
April 1, 2011 at 7:40 am
Hi Raymond, i am having the exact same problem. How and where did you manage to locate the information through the servcie broker? It's doing my nut in!:w00t:
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy