How do I restore a SQL Server backup to a different server that will synchronize the users correctly?
0
votes
1
answer
492
views
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?
Asked by JOE_JOE
(1 rep)
Jan 17, 2023, 07:57 PM
Last activity: Jan 20, 2025, 03:03 AM
Last activity: Jan 20, 2025, 03:03 AM