0

The issue I am having is I have 2 servers A and B. On A I have a dB, say it's called "MyDB"... On server B there are 2 dBs called "MyDB_Dev" and "MyDB_Test". These all were set up upon my arrival to the client. On server A and B there are Users, Rolls and schemas set up using the same name (e.g., User1, User2, User3, etc.) The issue is that the client wants to stand up a new dB on server B called "MyDB_Stage". What I did was run a backup of the production db and then restored it to server B. The issue is that the user rights will not allow them to log into the staging dB using their user id. What I don't understand is if I open the server user login list the login is not associated to the staging dB - seems fine because I should be able to simply add a check to the new dB, however, when I try it tells me the dB already is assigned that user. I then tried to delete the user from the dB but I get an error that the schema is being used by another login. For this system there are a lot of users, rolls, and schemas and I would hate to have to remove everything and have to put them back. Any advice?

1

1 Answer 1

0

When you create users on server A and B manually, they will have another SID (Security Identifier). In this case, I think it would be better to copy the users from server A to server B, with keeping the original SID in place. In the user database, the SID is used. To copy the users you can make use of the sp_help_revlogin stored procedure. See link.

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/security/transfer-logins-passwords-between-instances

1

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.