Solved

Transfer SQL Database

Posted on 2003-11-26
12
428 Views
Last Modified: 2012-06-27
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.

Thanks












0
Comment
Question by:Jonathan Kelly
12 Comments
 
LVL 3

Expert Comment

by:Flynnious
ID: 9824480
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.

HTH

Flynnious
0
 
LVL 7

Author Comment

by:Jonathan Kelly
ID: 9824656
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.







0
 
LVL 3

Expert Comment

by:Flynnious
ID: 9824691
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.
0
 

Expert Comment

by:JasonSpezza
ID: 9845081
Don't restore master.  Use this instead:

http://support.microsoft.com/default.aspx?kbid=246133

0
 
LVL 7

Author Comment

by:Jonathan Kelly
ID: 9849090
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 ?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 7

Author Comment

by:Jonathan Kelly
ID: 9849282
this is urgent - i need some advice

i cannot effect the live installation in any way
0
 
LVL 7

Author Comment

by:Jonathan Kelly
ID: 9849795
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?




 

0
 
LVL 3

Expert Comment

by:Flynnious
ID: 9849857
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 )

Cheers,

Flynnious
0
 
LVL 7

Author Comment

by:Jonathan Kelly
ID: 9918993
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!!http://support.microsoft.com/default.aspx?scid=kb;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 ??

0
 
LVL 7

Author Comment

by:Jonathan Kelly
ID: 9919004
these are the steps to fix the problem
Steps to fix:

1. Open Query Analyzer and connect to SQL Server using as 'sa'
OK
2. Get the details of the user that is the dbo of the database right now:
   use database
   go
   select master.dbo.sysxlogins.name from master.dbo.sysxlogins, sysusers
   where sysusers.name = 'dbo'
   and master.dbo.sysxlogins.sid = sysusers.sid
OK
3. Change the ownership of the database from the current integrated login to 'sa' as follows:
   use database
   go
   exec sp_changedbowner sa
OK
4. Grant the Ingtegrated Login access to SQL Server if not already done as follows:
   use master
   go
   exec sp_grantlogin <above_login>
   go
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>
   go
WHO IS<new user> ???

0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 11533600
PAQed, with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now