1

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'.
5

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.

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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