Cannot drop orphaned account on my SQL 2012 Server

Question

Cannot drop orphaned account on my SQL 2012 Server

marco.benaglio

Old Hand

Points: 353
More actions
December 30, 2014 at 6:51 am

#293868

Hi all,
I have a terrible issue that I am not able to solve by myself. I have made a lot of research on internet but I still not found a valid solution for the problem.
All is happened when a server crashed some weeks ago and it was removed from my network.
After that, under my SQL Server 2012 I get an orphaned account which cannot be removed. This account is a computer account related to an old SCOM installation.
If I try to execute the command [font="Courier New"]DROP USER [NETWORK\SERVERNAME$][/font] I get the following error message:
[font="Courier New"]The database principal has granted or denied permissions to objects in the database and cannot be dropped. Msg 15284, Level 16, State 1, Line 1[/font]
The database principal has granted or denied permissions to objects in the database and cannot be dropped.
But if I run the following command to know all permission granted to the account, I get an empty result:
[font="Courier New"]select * from sys.database_permissions where grantee_principal_id = user_id ('Network\SERVERNAME$');[/font]
Furthermore, following the instructions provided by the official KB for troubleshooting orphaned users, I get another error (http://support.microsoft.com/kb/274188/en-us).
[font="Courier New"]sp_change_users_login 'update_one', 'Network\SERVERNAME$', 'Network\SERVERNAME$'
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114
Terminating this procedure. The User name 'Network\SERVERNAME$' is absent or invalid.[/font]
The only thing I can retrieve is 15 permissions that this account granted to another account in the past:
[font="Courier New"]select *
from sys.database_permissions
where grantor_principal_id = user_id ('Network\SERVERNAME$'
--
class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc
17 SERVICE 65538 0 5 19 SN SEND G GRANT
And more 14 rows…[/font]
Anyone can help me to resolve my issue and safe delete the account SERVERNAME$? I need this because I have to reinstall SCOM with the same computer name on another server, but installation fails due to this behavior.
Thanks to all in advance.
Marco

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

The error message is saying that the principal has granted or denied permissions - not that it has had permissions granted to or denied from it. If you revoke those 15 permissions that you found and then recreate them with a user that you're not trying to delete, you should then be able to delete the user.

John

marco.benaglio Old Hand Points: 353 More actions · Answer 2

Hi John,

thanks for your reply. Can you help me on perform the permission change?

I ask you because these permissions are "SEND PERMISSION" for a local service (http://technet.microsoft.com/en-us/library/ms166038(v=sql.105).aspx) but I am not able to identify the service where I have to drop the permission:

GRANT SEND ON SERVICE::[how-can-I-find-the-name-of-the-scom-service?]

TO [Network\SERVERNAME$] ;

GO

Thanks,

Marco

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Marco

I'm getting a bit outside my area of knowledge now. What does the row in sys.database_permissions look like?

John

marco.benaglio Old Hand Points: 353 More actions · Answer 4

Hi John,

the rows look like a "SEND" permission on some object, but I don't know how identify which object... any idea?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

John Mitchell-245523

SSC Guru

Points: 148809

December 30, 2014 at 10:00 am

#1767035

Marco

Please will you post the actual row(s)?

Thanks

John

marco.benaglio Old Hand Points: 353 More actions · Answer 6

Hi, the output is:

class;class_desc;major_id;minor_id;grantee_principal_id;grantor_principal_id;type;permission_name;state;state_desc

17;SERVICE;65538;0;5;19;SN;SEND;G;GRANT

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

What results do you get from the following queries? Remember to run them in the same database as you got the sys.database_principals results from.

SELECT name FROM sys.objects WHERE object_id = 65538

SELECT name FROM sys.database_principals WHERE principal_id = 19

John

marco.benaglio Old Hand Points: 353 More actions · Answer 8

Hi man,

thanks a lot for your support! With your suggestion, I solved the problem using the following command:

DECLARE @service_name sysname,

@username sysname

SELECT @service_name = name FROM sys.services WHERE service_id = 65538

SELECT @username = name FROM sys.database_principals WHERE principal_id = 5

DECLARE @sql nvarchar(MAX) = 'REVOKE SEND ON SERVICE:: ' +

quotename(@service_name) + ' FROM ' + quotename(@username)

PRINT @sql

EXEC(@sql) AS 'Network\SERVERNAME$'

Thank you very much!

Marco