dbi Blog

Few weeks ago, I have a little issue when I try to drop a database-user without login

Unfortunately, I do a little mistake at the beginning…
I receive like every morning a report if all AD logins (computers, groups, users) registered on SQL server instances are in the AD with the useful command sp_validatelogins
This report indicates that a computer name dbi\server_name$ was no more in the AD.
I drop the login without problem and without verifying the binding with database-users (this was my mistake…). 😕

The day after, I receive another alert that I have an orphan database-user on the SCOM database OperationManager12.

My reaction was to connect to the instance and go dropping the user like usual when I become this alert.

As you can see, I receive the error message:
Msg 15284, Level 16, State 1, Line 15
The database principal has granted or denied permissions to objects in the database and cannot be dropped.
I “google” the error and found some explanations.
The user is owner of services in the service broker and I use this query to find the message:

select * from sys.database_permissions where grantor_principal_id = user_id('dbi\server_name$')

The user is linked to a service number 65536. I search now the service linked to this number.

With the name of the service, I can revoke the SEND permission from this user.

And I receive this error:
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
With the Google’s help, I re-try with the EXECUTE AS command with the server name as user:

EXECUTE AS USER= 'dbi\server_name$'
REVOKE SEND ON SERVICE::Service_mid10_39_40_55_pid4288_adid2_r479087710 FROM [dbi\server_name$]
REVERT

As excepted, I receive a new error:
Msg 15404, Level 16, State 11, Line 37
Could not obtain information about Windows NT group/user ‘dbi\server_name$’, error code 0x534
The login does not more exist, then it’s normal to have this error.
And now, what to do?
The only workaround that I found, is to drop the service, drop the user an recreate the service with dbo as owner(before dropping, create the create service statement before):

DROP SERVICE [Service_mid10_39_40_55_pid4288_adid2_r479087710]
GO

USE [OperationsManager12]
GO
DROP USER [dbi\server_name$]
GO

CREATE SERVICE [Service_mid10_39_40_55_pid4288_adid2_r479087710]  ON QUEUE [dbo].[Queue_mid10_39_40_55_pid4288_adid2_r479087710] ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])
GO

Et voila! It was a little bit tricky to find out a solution but this one works! 😎