Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Transfer SQL Database

Posted on 2003-11-26
12
Medium Priority
?
455 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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
 
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

927 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