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.
· Or if these machine accounts belong to 2 machines that were cloned and SID was never regenerated for one of the cloned machines.
· Nevertheless, Machine SID duplication does not cause problems as suggested in the following blog https://blogs.technet.com/markrussinovich/archive/2009/11/03/3291024.aspx
· But, in our case duplicate machine account SID is causing the above issue.
· If the login that had the SID already is not in use or if it is Obsolete ; Drop the Culprit Account <DOMAIN NAME>\<MACHINE NAME>$ using the following command:
· DROP LOGIN [<DOMAIN NAME>\<MACHINE NAME>$]
· However deleting server logins does not delete the database users associated with the logins. To complete the process, delete the users in each database. It may be necessary to first transfer the ownership of schemas to new users.
o You can use the below script by replacing the <Domain Name>\<Machine Name>$ with the Culprit account, to identify the objects owned by the users and schemas mapped to it.
Set Nocount on
Declare @OrphLogins Table (SID Varchar(200), NTlogin Varchar(200))
-- Inserting the orphaned NT user into temp table
Insert into @OrphLogins select SID,name from sys.server_principals where name = '<Domain Name>\<Machine Name>$'
--Display the number of Orphaned Users
Select NTLogin As "Orphaned Logins" From @OrphLogins
DECLARE @Login varchar(200)
DECLARE Orphcursor CURSOR FOR
SELECT NTLogin from @OrphLogins
OPEN OrphCursor
FETCH NEXT FROM OrphCursor INTO @Login
WHILE @@FETCH_STATUS = 0
BEGIN
Declare @TSequel Varchar(MAX), @DatabaseO Varchar(MAX)
Select @DatabaseO = ' SrPri.name COLLATE DATABASE_DEFAULT as Login, DbPri.Name COLLATE DATABASE_DEFAULT as [User],
orph.name COLLATE DATABASE_DEFAULT As [Name],
orph.type_desc COLLATE DATABASE_DEFAULT As [Object Type]
From %D%.sys.objects orph
Join %D%.sys.database_principals DbPri ON Coalesce(orph.principal_id,
(Select Sch.Principal_ID From %D%.sys.schemas Sch Where Sch.Schema_ID = orph.schema_id)) = DbPri.principal_id
Left Join %D%.sys.server_principals SrPri On SrPri.sid = DbPri.sid '
Select @TSequel = 'SELECT * FROM
(Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '
+ Replace(@DatabaseO, '%D%', [name])
From master.sys.databases
Where [name] = 'master'
Select @TSequel = @TSequel + 'UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', '
+ Replace(@DatabaseO, '%D%', [name])
From master.sys.databases
Where [name] != 'master'
Select @TSequel = @TSequel + ') LL Where Login = ''' + @Login + ''''
--print @sql
EXEC (@TSequel)
FETCH NEXT FROM OrphCursor
INTO @Login
END
CLOSE OrphCursor
DEALLOCATE OrphCursor
GO
o Please note that this script only lists the objects and the user details but no action in terms of transferring the ownership of the schemas or deleting the users is done. The above script is taken from the blog: https://blogs.msdn.com/sqlserverfaq/archive/2009/10/13/script-to-find-the-objects-owned-by-orphaned-users.aspx.
Try creating the Login now, it should work
Jwalanth BJ Chavan
SE, Microsoft SQL Server.
Reviewed by
Shamik Ghosh
Technical Lead, Microsoft SQL Server.
Amit Banerjee
Support Escalation Engineer, Microsoft SQL Server.