Skip to content

SQL Server Tips: Drop a database-user attached to a service…

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.
error_drop_server01
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$')

error_drop_server02
The user is linked to a service number 65536. I search now the service linked to this number.
error_drop_server03
With the name of the service, I can revoke the SEND permission from this user.
error_drop_server04
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

error_drop_server05
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

error_drop_server06

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

error_drop_server07

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

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

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Stéphane Haby

Stéphane Haby

Stéphane Haby has more than 20 years of experience in Microsoft solutions, specializing in the installation, migration, best practices, and performance analysis of Microsoft SQL Server, as well as Microsoft Business Intelligence solutions (SharePoint, Office) and .NET development for the banking sector. A former Microsoft Most Valuable Professional (MVP) and early adopter of Microsoft Team System in France, he has authored several technical articles. He holds Microsoft Certified Solutions Associate (MCSA) and Expert (MCSE) for SQL Server, MCTS, MCITP for SQL Server 2008, and ITIL V3 Foundation certifications. His industry experience covers chemicals and pharma, banking, and financial services.View Author posts

182 posts since April 2011

Leave a Comment

Your email address will not be published. Required fields are marked *