"CREATE LOGIN ... FROM WINDOWS" - incorrect SID

• radek.hruby

  SSCrazy

  Points: 2123

  More actions

  January 7, 2014 at 3:04 am

  #284103

  Hi experts,

  I'd like to get an advice about an issue I've seen in two environments. Both are related to SQL Server 2012 and AD. Our application needs to use AD computer account as the SQL login so the first step when we add a new server to the database is:

  CREATE LOGIN [<DOMAIN>\<SERVER>$] FROM WINDOWS;

  then obviously CREATE USER .... etc.

  This works perfectly in 99.9% but I've seen twice in my life that the login has been created with an incorrect SID i.e. the server couldn't login to the SQL Server. So far I've been able to narrow down the issue to the database context under the command is being executed - which doesn't make much sense to me. What I'm trying to say is that:

  USE <OUR_DB_NAME>;

  GO

  CREATE LOGIN [AAA\BBB$] FROM WINDOWS;

  creates the login with an incorrect SID. Can be checked by "SELECT [sid] FROM sys.server_principals WHERE name LIKE 'AAA\BBB$'];

  but

  USE master;

  GO

  CREATE LOGIN [AAA\BBB$] FROM WINDOWS;

  creates the login with the correct SID.

  Would someone has an explanation?

  Thanks,

  Radek