0

I have a database server with several databases inside. This server is used as test server to store a set of test databases. The test databases do not come from the same production server thus I can have a mixed context where multiple test databases in test server come from different production servers. The logins and the mapped users in all the production servers have the same name but indeed their login passwords are different.

I use a Powershell Azure CLI script in order to copy the production databases from their source production server and restore them in the unique target test database.

The issue is that there is an inconsistent state between the mapping of the logins in the test server and the user in the test database, since even though they got the same name their identifier is probably different.

How can I map the test server login password to each user in the databases by using the name as a matching criteria?

Improve this question
2
  • 2
    When you create the login(s) on the second server you should be specifying it's SID; this means that the users in the restored database aren't orphaned. If the logins already exist, and already have different SIDs, you'll need to ALTER the users so that they are linked to the right LOGIN; which presumably is the one with the same name (though this is not always the case).
    – Thom A
    Oct 11, 2021 at 10:22
  • 3
    Does this answer your question? Is there a shorthand way to 'Auto_Fix' all Orphaned Users in an SQL Server 2008 R2 database?
    – SMor
    Oct 11, 2021 at 11:08

2 Answers 2

Reset to default
2

As the databases user are identical across the servers, you can simply maintain the SID same for the logins across all the SQL Servers while the purpose of the login/users is same. Following is an example to create login with same SID, this is onetime activity across the servers (while you aware of server level permissions).

create login test_login_1 with password = 'MyStrongPassword', sid = 0x76CED6A5F3FA0000378945348A64FB67;

Alternatively, as an extension to your existing PowerShell script, you can use Copy-DbaLogin from dbatools.io to replicate the logins.

Improve this answer
0

There is sp_change_users_login stored procedure in SQL sever, which can be used for this purpose.

More information on here.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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