September 12, 2007 at 1:29 pm
Ok, using SQL 2000 for moving a server, I use to create the users on the new server and then just create a dummy database in preperation for the move of a database. I would then just restore over the top of the dummy database with the production database and I was all set. Since all the users were aleady there and were already assigned to the dummy database that is the same exact name as the production database, the restored database was perfectly in sync and the users's never new it was moved.
Now, with SQL 2005, I do the same thing, or even just restore a new copy of the database over to a test or new server were the login's already exist and are already assigned to the database of the same name and it does not work. The user's connect but can't get to the database, and if the database is there default, they can't connect becuase there is an error connecting to there default database. I have to go in, remove the schema's and users from the database and then go and re-assign them to the database.
Why is this happening, I would think that it should work the same as 2000 did, what has changed that is making me change the way I have done restore's.
September 13, 2007 at 11:46 am
Check out "sp_change_users_login" (at least, that's what it was called in SQL2K - it probably has the same name in SQL2005). What's happening is that both the master database and the individual databases store user login information, including SIDs. When you restore a database onto another server the SIDs are out of synch and you can't log in.
BTW - I see this all the time in SQL2000. Consider yourself lucky you haven't run into it before.
Steve G.
September 13, 2007 at 12:05 pm
Yes, I did find that out with a message from another board. I actually created a script using the sp_change_users_login report action to a temp table and then took the list from the temp table in a cursor to run the auto_fix against all the user's that were reported as being a problem.
Thanks,
April 7, 2011 at 11:31 am
/* This seems to sync the SIDs between login and database user in SQL 2008 */
ALTER USER [Mary5] WITH login=[Mary5];
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply