Background:
Developing a script to replicate two databases from one SQL Server 2005 Express to another on a nightly basis. The approach I am taking to achieve this is to perform a nightly backup of the master database, and transfer and restore tha backup on the mirror server. This ofc means that I need to sort out the orphaned users, which for the most part is working, except for the dbo user.
This is what I am doing currently to restore each database
ALTER DATABASE [db1] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [db1] FROM DISK = N'\path\to\db1.bak'
USE [db1]
EXEC sp_change_users_login 'update_one','user1','user
1'
EXEC sp_change_users_login 'update_one','user2','user
2'
However when I come to try and restore the dbo login to user2 which is how its mapped on the master
EXEC sp_change_users_login 'update_one','dbo','user2'
I get the error
Msg 15287, Level 16, State 1, Procedure sp_change_users_login, Line 35
Terminating this procedure. 'dbo' is a forbidden value for the login name parameter in this procedure.
Well for starters, I didnt pass dbo as the login name its passed as the username, which led to some confusion at first, but it seems I am not allowed to reconnect dbo user to its login in this way. So I instead do.
EXEC sp_changedbowner 'user2'
And the dbo user is now connected to its login. However, the user2 login on the live server also has the following mappings
db1 User=dbo Default Schema=dbo
db2 User=user2 Default Schema=dbo
and on the restored mirror, the first mapping is lost, so I need to re-apply it, but when I try:
CREATE USER [dbo] FOR LOGIN [user2]
it complains that user dbo already exists, which ofc it does.
Right, so the question is:
How do I re-create user mapping between the user2 login and the db1/dbo user?
Start Free Trial
