2

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?

Improve this question

1 Answer 1

Reset to default
4

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.

Improve this answer
2
  • 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?
    – variable
    Commented Jun 7, 2023 at 11:12
  • 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?
    – Tibor Karaszi
    Commented Jun 7, 2023 at 11:27

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.