locked
Windows users SIDS and sp_help_revlogin RRS feed

  • Question

  • To move logins to a new server I was planning to use sp_help_revlogin. I noticed  that  sp_help_revlogin generates SIDs for SQL logins but not for Windows logins. Does anyone understand why that is? something different about SQL And Windows login SIDs?

    thanks

    Friday, May 24, 2013 7:00 PM

Answers

  • Yes. SIDs for SQL logins are generated locally, and if you were to create new logins without bringing the SID over, the SID in master, would not match the SID for the database users, which would make all those users orphaned.

    A Windows Login has it SID taken from the AD, so the SID will be the same on the new server. If the new server is in the same domain, that is. But whatever, you cannot set the SID for a Windows login, only an SQL login.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by peterfeakins Friday, May 24, 2013 9:35 PM
    Friday, May 24, 2013 9:27 PM

All replies

  • Yes. SIDs for SQL logins are generated locally, and if you were to create new logins without bringing the SID over, the SID in master, would not match the SID for the database users, which would make all those users orphaned.

    A Windows Login has it SID taken from the AD, so the SID will be the same on the new server. If the new server is in the same domain, that is. But whatever, you cannot set the SID for a Windows login, only an SQL login.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by peterfeakins Friday, May 24, 2013 9:35 PM
    Friday, May 24, 2013 9:27 PM
  • Thanks
    Friday, May 24, 2013 9:35 PM