user mappings getting messed up when I restore a database to integration server

Question

user mappings getting messed up when I restore a database to integration server

sqlguy-736318

Hall of Fame

Points: 3859
More actions
October 30, 2011 at 7:19 pm

#250215

I have a SS2008 database. I make changes to it in my local environment, save the bak file to the integration server and then restore the bak file to the SS instance on the integration server.
For some reason my login user mappings get messed up when I try to use this deployment approach.
* I have a MyLogin database user
* My MyLogin database user has db_datareader and db_datawriter permissions
* I have a MyLogin login in my server security
* My MyLogin login has a user mapping to MyLogin database user in my database
However, whenever I restore a new copy of my database into this configuration I can no longer login with my MyLogin user credentials.
I have to delete MyLogin database user and then create a new database user mapping from MyLogin in my database to a new MyLogin database user.
Can someone please explain if this is the expected behavior or if I'm doing something wrong here? It's kind of a hassle to have to go through this reconfiguration process everytime I deploy a new version of the database.....

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Expected behaviour because when you created the login on the other server you used the same name but did not specify the SID. Hence the SID is different between the servers, hence the logins don't match.

Google - orphaned users.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

t_a_dunn SSC Rookie Points: 45 More actions · Answer 2

You can associate (re-sync-if you will) database users with server logins on the destination server using the sp_change_users_login procedure. For example:

exec sp_change_users_login 'UPDATE_ONE', 'dbUserName', 'loginName'

See: http://msdn.microsoft.com/en-us/library/ms174378(v=SQL.100).aspx

JonFox SSCrazy Points: 2157 More actions · Answer 3

Actually, sp_change_users_login is deprecated in 2008. It has been replaced by the ALTER USER statement.

ALTER USER [username] WITH LOGIN = [login name]

sqlguy-736318 Hall of Fame Points: 3859 More actions · Answer 4

I tried:

alter user [MyUser] with login [MyLogin]

However, SS returns the following error: "Incorrect syntax near 'MyLogin'."

I tried executing this query in the main app database as well as master and got the same error when executing in the context of either database....

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

You left out the =

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlguy-736318 Hall of Fame Points: 3859 More actions · Answer 6

GilaMonster (11/3/2011)
You left out the =

Thanks Gail - that was the problem.....