November 20, 2007 at 11:24 am
First in my defense I am a database developer, I usually hack TSQL, never claimed to be much of a production style DBA but sometimes you gotta do it yourself to get it done.
I needed to move a database from one server to another, both were SQL Server 2000 SP4 running on Windows Server 2003 SP2. I generated a script of the database I needed to move and ran it on the new server creating the empty database for me to restore to. I then took a complete backup of the database I needed to move. When I restored the the database, the users were assigned to database but were not mapped to their logins or their roles. I tried this a few times with the same result. Both through the GUI and through TSQL scripts. Any suggestions would be greatly appreciated. Once I figure this out there are 60 more to move, so you can see why I need to do this during the restore or with a script after, reassigning by hand is not a viable option for that volume of DB's.
Thanks All.
DumbDBA
November 26, 2007 at 5:29 am
Bob,
your problem is that while the users are stored inside the database and part of the backup, the logins are stored in the master database and thus not included win the backup.
Two things you need to do:
1. Create the logins needed on the new server. Either create scripts or use the Transfer Logins task in DTS.
2. After restoring the database you need to fix all user with a SQLLogin by running sp_change_users_login. If you do a search on this site you should find several scripts for this task.
Hope this helps
Markus
[font="Verdana"]Markus Bohse[/font]
November 26, 2007 at 5:39 am
Hi,
In this link you'll find transfer logins script provided by Microsoft http://support.microsoft.com/kb/246133
Regards,
Ahmed
November 27, 2007 at 4:44 am
If you have 60 odd databases to move and use SQL authenticated ids a lot you will have a lot of sp_change_users_login coding to do. (windows authentiacated users will be fine as long as login already present) If that is the case you will be better off using the sp_help_revlogin script to copy the logins over as it maintains the suid and the logins\users will automatically match. (google the script, you will find it easy).
You may need to edit the script output to only load logins you want.
If you have to use the dts transfer login task use the following to reverse engineer the sql to fix the users:
use db_name
go
--Reverse engineer sp_change_user
select 'sp_change_users_login "update_one",' + name + ','+ name + CHAR(13) + 'go' from sysusers
go
-- Run this once you have reset all the users to check that there are no orphans
sp_change_users_login 'report'
go
---------------------------------------------------------------------
November 27, 2007 at 5:00 am
The best and easiest way is using DTS Transfer Login option
November 27, 2007 at 5:09 am
dhara (11/27/2007)
The best and easiest way is using DTS Transfer Login option
if you need to keep logins up to date on a constant basis in say logshipping then I agree with you, but for a one-off task such as this with so many databases, I am not so sure.........
no I am sure, I would go the script route.
---------------------------------------------------------------------
November 28, 2007 at 12:39 am
hI,
HOW TO CREATE THE LOGIN ONTO THE LIVE DATABASE AFTER THE DATABASE RESTORATION
1.Open Query Analyzer into the backup server.
2.Select your database name.
3. Type the following query.
-------------------------------------------------------------------------------------
print ''
print '-- Script for restoring standard sql logins.'
print '-- As found on ' + @@servername + ' per ' + cast(getdate() as char)
select cast('exec sp_addlogin ''' + upper(a.loginname) + ''', ' as varchar(40)),
convert(varbinary(64),b.password),
cast(',''' + 'YOURDBSNAME' + ''', ' +
'''' + 'us_english' + ''', ' as varchar(35)),
convert(varbinary(64),b.sid),
',''skip_encryption'' '
from master.dbo.syslogins a,
master.dbo.sysxlogins b
where a.isntname = 0
and a.sid = b.sid
and b.name not in ('sa','repl_publisher','repl_subscriber')
order by b.name
Print ''
-----------------------------------------------------------------------------
4.Press F5 to run the query.
5.It will generate the output. Each line will contain the login information of one user. Copy the output.
6.Open Query Analyzer into the Live Database.
7.Select YOURDBSNAME as the database.
8.Paste the copied information.
9.Go through all the user logins and delete those users (lines) that are already present into the Live Database. Also delete the last line that is (xxx row(s) affected).
10.Press F5 to sun the query.
11.It will create the logins onto the live database.
November 30, 2007 at 12:17 pm
Hello Markus, I have some questions, just trying to understand the process, your indications and the best choice among several options.
I have read some comments about using DTS Transfer Logins, sp_help_revlogin, sp_change_users_login if one is better than other, if one should be applied before another, etc.
So I'd like to feet ground about your recomendations.
First, I have to transfer logins between both servers using the Transfer Logins DTS, right ? Actually, what does this do ? Just copy the logins (user id, password, SID?) to the new server ? What if I don't have reach to the new server ?
Second, once I did this, I have to run sp_change_users_login in the new database, will this task "re-connect" the newly created logins with the users in the database ? The database was restored in the new server before this task ? Or should be restored before executing the DTS (step 1) ? How should I invoke this sp ? Should I apply a special parameter in order to fix all logins or must be done in a one-by-one basis ?
Third, why should I use the sp_help_revlogin ? Does this substitute the previous steps ? Or is it additional ?
Thanks in advance.
Best regards.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply