Transfer SQL Database

Hi Experts,

I need to setup a test environment for my live database running on SQL Server 2000.

I know I can use BackUp and Restore to copy the database to my test Server but how do I successfully transfer all the Logins and Users ?

I know the Logins are stored in the master database and the users are stored in the User database (lets call it MyDb).

If I restore MyDb to the test server the Users and Roles are created but the logins are not and so the SUID does not match so my users are 'Orphaned'.

So If I restore the Master database from the Live server to the Test server and then restore Mydb to the Test server will the SUID's match up ?

The Logins will be using integrated security. My Admin guy will setup a Test environment to match the Live environment exactly - same domain, server name, Windows Users etc..

The restore will happen on a regular basis and will completely overwrite the Test database.

Is there a better way to achieve this ?
What effect\implications are there when restoring the Master database from one sever to another server ?

Any Help and/or Advice is gratefully acceopted.


Jonathan KellyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If the User (for the database) doesn't exist as a login (for the server, stored in master database) you can create it by going to logins and creating a new login with the right name, and giving it access to the restored database.  It will error saying "User already exists" because it can't create a user with that name, but that will happen after it's created the login, so just accept the error.  You'll find you've got a user for the database and a login with the same name, and you just have to link the login with the user.  You do this by logging on to the server (in query analyzer) as an admin user (either sa or windows authentication or similar), selecting the newly restored database and running:

sp_change_users_login 'update_one','<user name>','<login name>'

where <login name> is the new login and <user name> is the new user.


Jonathan KellyAuthor Commented:
Thanks Flynnious.

So your saying that if I restore Mydb to the test server and then

1. Create a login for each user - ignore the error
2. Use sp_change_users_login 'update_one','<user name>','<login name>' to align the Login with the User in the restored database - this will align the SID's ??

all my users will have access rights identical to those on the Live DB ?

Will this work with integrated security ?

Is there any way of automating the Task using SQL_DMO to read the users, create a Login and then execute sp_change_users_login .

I was hoping to avoid using sp_change_users_login by restoring the master database from the live server to my test server.

The db users are part of a Win NT group and it is the group that has been added to the SQL Server as a user - MyDomain\MyGroupName.

I don't know about integrated security.  I also don't know about moving restoring the master database.  I would've thought that would be a very, very bad thing to do, but that's purely a guess.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Don't restore master.  Use this instead:

Jonathan KellyAuthor Commented:
Thanks  JasonSpezza,

I cannot run a script on the source database\server.

I have a copy of the live Master db.

If I setup a server and restore the Master db to that server and then run the script in the above link will that give the output i need ?
Jonathan KellyAuthor Commented:
this is urgent - i need some advice

i cannot effect the live installation in any way
Jonathan KellyAuthor Commented:
I have been messing around with two test servers and a dummy database.

The database has a number of Windows Users.
My destination server does not have any logins outside of dbo and Builtin Admin

After I restore my dummy db to the dest server I run sp_GrantLogin for each user of my database.
This creates a login for each user and everything seems to work fine!

Furthermore if I drop my dummy database from the dest server without deleting the logins, when I restore the database again (to the dest server) the Logins and Users seem to match themsleves up and everything works fine.  If this happens with my Live database then great but shouldnt the User and Login SIDS become mismatched ??????

Same Domain
Different SQL Servers

whats going on?


I don't know much about Windows authentication logins, as I hardly ever use them.  It could be that that's how MS intended authentication to be done in SQL, and that's why it works properly.  Don't forget that losing the connections between users and logins is actually a bug in the software, and not the way it's supposed to work, so the sp_change_users_login process is only really a work-around.

Anyway, I hope what you've found out actually works for your live server and solves your problem (not least because I don't have a clue otherwise :-P )


Jonathan KellyAuthor Commented:
At my clients site the above didnt work unfortunately. Something to do with SQL Server not recognising it was in the same domain (it wasnt but the domain names were identical!).

I created the Logins and ran sp_sidmap which seems to have done the trick - woohooo!!;EN-US;240872

The only problem i have now is that the owner of the database is removed.
Along with the sp_sidmap SP there is a readme file which describes the steps needed to fix the problem. Problem is I dont understand what step 4 and 5 are about.
Anyone used sp_sidmap before ??

Jonathan KellyAuthor Commented:
these are the steps to fix the problem
Steps to fix:

1. Open Query Analyzer and connect to SQL Server using as 'sa'
2. Get the details of the user that is the dbo of the database right now:
   use database
   select from master.dbo.sysxlogins, sysusers
   where = 'dbo'
   and master.dbo.sysxlogins.sid = sysusers.sid
3. Change the ownership of the database from the current integrated login to 'sa' as follows:
   use database
   exec sp_changedbowner sa
4. Grant the Ingtegrated Login access to SQL Server if not already done as follows:
   use master
   exec sp_grantlogin <above_login>
WHAT IS <above_login> and what Integrated Login are they talking about ?
5. Now change the database ownership back again:
   exec sp_changedbowner <new_user>
WHO IS<new user> ???

PAQed, with points refunded (500)

Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.