0

SQL Server logins (server principals) have an SID associated with them. This is used to map logins to database users.

I'm using the script to transfer logins to another SQL Server instance: Transfer logins and passwords between instances of SQL Server

The generated output has got the SID value for SQL Server logins, but there is no SID for windows users and groups. This is understandable for AD windows users and groups since their SID is derived from the AD. However for local windows users, groups and local service accounts the script doesn't include the SID.

For example: NT SERVICE\MSSQLSERVER, NT SERVICE\SQLSERVERAGENT, NT AUTHORITY\SYSTEM and my local windows users (PCNAME\USER1) don't have an SId generated by the above script.

I realize that the local SQL Server service accounts (listed above) have the same SID on every SQL Server instance. Maybe that is why their SID isn't included in the script.

But what about local windows users? Will these definitely be orphaned when using this script or is there any workaround to ensure that the local windows users aren't orphaned?

0

1 Answer 1

3

SID of windows domain logins can't be different between instances in the same domain, so there is no orphaned users when you copy logins from an instance to another and that's why you can't specify SID of windows logins when you are creating them.

Local windows login SIDs can't be the same in different machines of the same domain, so you can't avoid having orphaned users. They can't even have the same login name (es: machine1\login1 is different in both name and sid from machine2\login1)

Refer to the view sys.server_principals as reference for names and SIDs.

2
  • I have managed to keep same account name like PCNAME\USER1 by having the computer on separate domain.
    – variable
    Sep 9, 2022 at 7:20
  • that ok. so you have two machines with the same name joined to different DOMAINS. in that case you can have the local login with the same name but due to the randomness of SID they have different sid. This generate orphaned usuer in database and can't be prevented
    – MBuschi
    Sep 9, 2022 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.