How to migrate local windows users, groups and service accounts without causing orphaned users?

Question

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?

Answer 1

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.