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.
No comments:
Post a Comment