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

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

  • Comments 1

 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 http://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: http://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.

 

Leave a Comment
  • Please add 5 and 2 and type the answer here:
  • Post
  • Thank you this helped me to resolve the issue

Page 1 of 1 (1 items)