3

I've been using this site for a while and this is the first time I've had to ask a question, but just wanted to say "Thank You" to everyone who has helped me in the past (without knowing they did so).

First things first.. I am trying to delete a user. To locate the user, I execute the following:

USE [MASTER]
SELECT SUSER_SID('henryhr')

Which returns:

0x01050000000000051500000084BC6CEC253AF11EEEFBBA6A240F0034

When I try this:

SELECT * 
FROM sys.database_principals 
WHERE sid = 0x01050000000000051500000084BC6CEC253AF11EEEFBBA6A240F0034

It returns 0 rows. If I try:

USE [MASTER]
DROP LOGIN henryhr

I get:

Msg 15151, Level 16, State 1, Line 1
Cannot drop the login 'henryhr', because it does not exist or you do not have permission.

I have tried creating another user and going through the same process and it works just fine. Any idea how to delete this?

2

Short intro

To connect to the server you have to have a login to access the server. To access a database you have to be a user in that database. All Logins are member of the server role public which can access the master database.

So to find users to drop within a database you query sys.database_principals and to find logins sys.server_principals. When you drop a login it's not removed from the user list of the databases and the SID is there until you drop the user.

So henryhr exists as a user in the master database but not as a login on the server so try use master; drop user henryhr; to drop the user henryhr from the master database

  • When I execute: use master; drop user henryhr; I get: Cannot drop the user 'henryhr', because it does not exist or you do not have permission. I have checked every database for that login, and I do not see it anywhere. Yet executing: SELECT SUSER_SID('henryhr') Still returns: 0x01050000000000051500000084BC6CEC253AF11EEEFBBA6A240F0034 – Tony May 8 '15 at 16:49
  • Does the user henryhr exist in sys.server_principals or sys.database_principals? If so, does he have an open session (select login_name from sys.dm_exec_sessions where login_name = 'henryhr' or original_login_name = 'henryhr') see answer from @Fred belove? – Spörri May 8 '15 at 20:46
0

I found this article that talks about a situation where the DBA received the same error (15151) and determined the issue was related to an open session for that user. He doesn't mention anything about not being able to find the user, but I figured I would pass this on just in case it helps.

Link to the article: SQL SERVER – Fix : Msg 15151, Level 16, State 1, Line 3 Cannot drop the login ‘test’, because it does not exist or you do not have permission

  • 2
    Good ole' Pinal Dave... If he got stumped, I don't feel so bad. I'm just going to do a 'Reboot Everything' approach this weekend and see if that fixes it. Thanks for posting the link! – Tony May 8 '15 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.