Sql-server – SQL Server 2008 R2 – Unable to delete Login

deletesql serverssms

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?

Best Answer

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

Related Question