Is there any risk with re-using sid from 1 sql server on multiple other sql servers when creating the same login?

Question

I'm setting up a sql login on a prod AG server. And creating it with same sid on the secondary AG node.

We also need this user on other prod standalone SQL servers, AG SQL servers, and dev sql servers.

Is there any risk with re-using sid from 1 sql server on other sql server when creating the same login?

The benefit is not having to handle orphan users after moving dbs across servers. Not sure if there are risks?

Answer 1

The potential issues would be:

1. The SID already exists as a login. Not likely, but theoretically possible. You won't be able to create the login if that is the case.
2. You have users the map to this SID that you are about to create a login for. If that user is supposed to map to the login, then great - this is what you were after! But if the user for some weird reason is a different user that happens to have the same SID (again, not likely but theoretically possible), then you have mapped the wrong login to the user.

In general, though, your situation is what the ability to specify the SID for a login is designed for.

• variable: What is the logic SQL uses to create sid for a sql login? Maybe there is a check if a login with same sid already exists?
• Tibor Karaszi: I don't know the algorithm, but there must be a check since you can't have two logins with the same SID. Possibly there is a datetime portion encoded in the SID?