Thursday, July 26, 2012

Drop orphaned user who holds SQL Server service

It's easy to google "The database principal owns a schema in the database, and cannot be dropped" error, but have you tried to google "The database principal owns a service in the database, and cannot be dropped."?

I tried, and the results were miserable -- I could find only half-baked solutions.
In my case I restored SQL Server backup database that had service broker turned on and then tried to drop orphaned user... unsuccessfully as you can guess.

Here's the solution.

1) Run this query:
select
 'alter authorization on service::[' + name + '] to dbo;' as [SQL to execute prior to dropping user]
from sys.services
where principal_id = user_id('MyServer\MyOrphanedUser');
2) Execute SQL that you get as a result of the query above. It would look something like that:
alter authorization on service::[SqlQueryNotificationService-25509d9f-4171-4701-817f-834f79a4a882] to dbo;

3) Finally you can drop your orphaned user:
drop user [MyServer\MyOrphanedUser];

Credits: How to disconnect service from orphaned user by Nimit Parikh

P.S.: I also tried:
delete from sys.services where principal_id = user_id('MyServer\MyOrphanedUser')
and got: "Ad hoc updates to system catalogs are not allowed." error.

2 comments:

Benna said...

I don't know if you will read this after so many years, but you save me. I got literally bold searching for this solution.

Many thanks, man!!

Dennis Gorelik said...

@Benna - I almost forgot that I struggled with these errors in the past (11 years ago).
Eventually I figured out, that the best solution - is to completely turn off such SQL notifications and rely on direct polling of SQL database tables (not reverse notifications of events).

Followers

About Me

My photo
Email me: blog@postjobfree.com