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 result of 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.

No comments:

Followers

About Me

My photo
Email me: blog@postjobfree.com