April 3, 2008 at 7:37 am
I write a lot of database driven websites because of this I'm constantly moving (and maybe I should not be moving, there might be a better way to somehow *update* s2 with s1's data) databases either as 'exports' or simply a copy and reattach. The problem I run into when I do this is that the database has 1 user associated with it (userXYZ) from the source database there is also a login for that user at the server (s1) level that associates that user to DBx, establishes userXYZ's roles, etc... subsequently that user also exists at the login level for the DB (the server will have 2 locations where the user exists one at the server level and the other within the DB).
When I move a DB (copy DBx from s1 to s2) the user and all of their objects within the DB come over just fine. But the new server s2 doesn't know at all who userXYZ is because there is no login for userXYZ under the server node [Security]-[Logins]-{userXYZ} there is only a userXYZ at the DB in [Server2]-[Databases]-[DBx]-[Security]-[Users]-{UserXYZ}.
So as a result I have to delete [Server2]-[Databases]-[DBx]-[Security]-[Users]-{UserXYZ} so that I can create him at this point [Security]-[Logins]-{userXYZ} and assign his roles/membership from there. But this has some problems involved. In order to delete [Server2]-[Databases]-[DBx]-[Security]-[Users]-{UserXYZ} I first have to delete *EVERY* object he owns, then delete the associated scheme [Server2]-[Databases]-[DBx]-[Security]-[Schemas]-{UserXYZ}. only then can I go ahead and create the user at this location [Security]-[Logins]-{userXYZ} and grant the roles/rights that will automatically put him in this location [Server2]-[Databases]-[DBx]-[Security]-[Users]-{UserXYZ} with the right roles.
Once the server has done this I then have to go in and create the schema I just deleted [Server2]-[Databases]-[DBx]-[Security]-[Schemas]-{UserXYZ} then I have to recreate all the objects (stored procedures) that I had deleted and if I'm lucky things will work after that.
=================================================
There has to be a better way! Please tell me there is a better way!!!???!!!??? :w00t::hehe::w00t::hehe::w00t:
(For those of you that remember Katie, we are doing okay. Your help then is the reason we are still here so thank you!)
Rex Winn
April 3, 2008 at 8:24 am
Create login XYZ on destination server and keep that all the time.
Whenever you restore DB1 from source to destination server this arrangement will work without removing anything from DB.
if the login XYZ has same password on both servers than only thing you need to do is add usermapping on destination server login
with appropriate perm. to your restored DB. If pw is different on both servers on destination server run
SELECT sid FROM dbo.sysusers WHERE name = 'xyz' [ on your user db at destination]
SELECT sid FROM master..sys.syslogins WHERE name = 'xyz'
This 2 queries will confirm that yes there is a mismatch
Apply
sp_change_users_login 'update_one', xyz, 'xyz'
and try the above said 2 select again to confirm that pw issue is fixed and you are all set.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
April 4, 2008 at 10:15 am
Thank you! That worked great!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply