The server principal '<domain>\User' already exists. But it doesn’t

Interesting little situation occurred today, the person I was working with was trying to add a login to a server and was getting the following error.

“The server principal '<domain>\User' already exists”

They were using a command like this and couldn’t figure out why they were getting the error above because when they looked at the logins setup on the system the login definitely didn’t exist.

CREATE

LOGIN [<domain>\<user>] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

The key here is that for windows logins the login is not the identifier for the login instead the SID is used, this is a binary value (varbinary(85)).

The nice thing is that the system security functions will work on accounts even if they aren’t logins in the sql server.

The function you need to use in this situation is SUSER_SID, you can pass in a login and get the SID back.

SUSER_SID('<domain>\<user>')

I did a quick query on the system tables for the SID and found the user already existed but with a different login name. The query I used was this.

select *

from sys.server_principals

where SID = SUSER_SID('<domain>\<user>')

How can this happen you ask? Well it is perfectly valid to rename user accounts but the SID stays the same.

Published Thursday, November 18, 2010 9:04 PM by simonsabin

Comments

Thursday, November 18, 2010 9:36 PM by SimonS Blog on SQL Server Stuff

# The server principal '<domain>\User' already exists. But it doesn’t

Interesting little situation occurred today, the person I was working with was trying to add a login

# Twitter Trackbacks for The server principal '&lt;domain&gt;\User' already exists. But it doesn???t - Simons SQL Blog [sqlblogcasts.com] on Topsy.com

Pingback from  Twitter Trackbacks for                 The server principal '&lt;domain&gt;\User' already exists. But it doesn???t - Simons SQL Blog         [sqlblogcasts.com]        on Topsy.com

# SQL Server 2005: domain\User already exists &laquo; SQLServerITA

Pingback from  SQL Server 2005: domain\User already exists &laquo; SQLServerITA

# SQL Server 2005: domain\User already exists &laquo; SQLServerITA

Pingback from  SQL Server 2005: domain\User already exists &laquo; SQLServerITA