Can I create a new user in MS SQL from windows and provide the SID

Question

I am trying migrate databases and along with the database I am trying to also migrate the users without having to re-map the users. I was trying to recreate the windows user with the old server SID. Is this possible? I have an example of the code I have been trying to use but SQL gives me an incorrect syntax error.

Query:

USE [master]
GO
CREATE LOGIN [domain\ntuser] FROM WINDOWS 
WITH 
DEFAULT_DATABASE=[master],
SID=0x01000000000000000000000000000000000000000000000000000000;
GO

Error:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '0x01000000000000000000000000000000000000000000000000000000'.

Answer 1

No, when creating a login from Windows, the SID is the Windows-provided one.

There is consequently no need to remap users; they automatically assume the same SID across servers within the same domain.

As an aside, instead of saying "SQL does not like it", you should provide the actual error message. It helps us provide a more meaningful answer, and in most cases, such as this, it also provides the actual answer.

Answer 2

No, it's not possible. For windows logins in SQL Server you cannot specify the SID, because it always matches the Windows user's SID. Using the WITH SID clause in the CREATE LOGIN command is meant for SQL logins, not for Windows logins.

The SID of windows users always depends on the issuer's SID (the machine in this case), so you can't even ask Windows to recreate users with the old SID