Unable to create a login in SQL Server 2005 and we get the following error The server principal '[\$]' already exists.

SYMPTOM
=======

·         When you try to create a login using the command "Create login [<Domain>\<Machine account>$] from windows" we might get the following error

o    Msg 15025, Level 16, State 2, Line 1
o    The server principal '<DOMAIN NAME>\<MACHINE NAME>$' already exists.
·         The following error might be returned when we try creating the SQL Server login in Management studio

o    Create failed for Login '<DOMAIN NAME>\<MACHINE NAME>$'.  (Microsoft.SqlServer.Smo)
o    The server principal ‘<DOMAIN NAME>\<MACHINE NAME>$’ already exists. (Microsoft SQL Server, Error: 15025)
·         The error message says that the server principal already exists. However, if you look for any such principal under Security in Management studio you will not find the login <DOMAIN NAME>\<MACHINE NAME>$
·         Further, when you run the following query in a new query window, you will not find the login <DOMAIN NAME>\<MACHINE NAME>$

o    Select LOGINNAME from sys.SYSLOGINS

CAUSE
=====

This problem occurs if there is already a login which is registered under the same SID as that of the Login which you are trying to add.

RESOLUTION
=========

·         To determine whether the SID already exists for a different login, please follow these steps:
·         In the new query window, run the following command:

SELECT SUSER_SID('<DOMAIN NAME>\<MACHINE NAME>$');
GO

·         Once the raw hex SID is retrieved run the following query against that SID to fetch the Server Principal name as shown in the below example:

Select * from sys.server_principals where SID=<SID found using the previous command>

o    This should give you the server principal that is already using the above SID.

·         Technically, it is not possible to have a more than one login with the same SID unless these logins have been manually created.

Read more: Microsoft SQL Server Tips & Tricks

Posted via email from jasper22's posterous