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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply