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:
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!!
@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).
Post a Comment