April 13, 2007 at 3:58 am
How do you normally transfer logins from a source server to a destination server, both SQL 2000?
It seems that people normally use DTS Trasfer Logins task, but I have some issues using it. Some people simply transfer the master database as well, but it may cause some other problems.
When using DTS Transfer Login task,
(1) Is it possible to retain the source server's SID value after trasferring to destination? I sometimes use the sp_change_users_login stored procedure to re-map the users and logins, but I am wondering if we can transfer logins in a simpler way.
(2) Does the login (SQL Server authenticated) have the same password as the source server after the transfer? If not how can we retain the password?
Thanks,
Del Piero
April 13, 2007 at 7:44 am
1) The answer is no.
2) It should have the same password. But I don't have my test server configured in a way here I can use the task. Simply create a test user with a simple password and transfer to a test and verify the same.
April 13, 2007 at 11:14 am
Can't keep SIDs, but you can use sp_help_revlogin to script out and move logins with the same passwords.
April 13, 2007 at 11:33 am
here is one script, that works if you create a linked server to the server you want to copy the logon from:
-- Setup a linked server called impserver from which the
-- standard logins needs to be transferred. You can call it
-- whatever you want & modify the linked server name also.
declare @login sysname , @password sysname
declare implogins cursor for
select name , password
from impserver.master.dbo.syslogins
where isntname = 0 and charindex( 'repl_' , name ) = 0 and
charindex( 'distributor' , name ) = 0 and name <> 'sa'
open implogins
while ( 'FETCH IS OK' = 'FETCH IS OK' )
begin
fetch implogins into @login , @password
if @@fetch_status < 0 break
exec sp_addlogin @login , @password , @encryptopt = 'skip_encryption'
end
deallocate implogins
go
Here is another script where rather than create a linked server you run this script on the server you are copying the logon from, the cut and paster the output to the new server and run:
SET NOCOUNT ON
SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
,', @deflanguage = ''' + language + ''''
,', @encryptopt = ''skip_encryption'''
,', @passwd ='
, cast(password AS varbinary(256))
,', @sid ='
, sid
FROM syslogins
WHERE name NOT IN ('sa')
AND isntname = 0
This script is easily adapted to only copy specific logins or only logins with a user in one particular database or whatever you need. If you are using SQL 2005 you may need to adapt the sp_addlogin command to use the CREATE LOGIN format instead.
Francis
April 14, 2007 at 6:12 am
Yes, as Steve stated, sp_help_revlogin will help you perfectly...
Thanks
Jay
http://www.sqldbops.com
April 16, 2007 at 5:36 am
sp_help_revlogin works great and is possibly the most useful script I have used.
However remember it does not set default database, default language or asign any server roles so you may need to take these into account via scripts.
DTS transfer login task does not assign server roles either. It sets defaultr language but seems unreliable on default database (does sometimes, others not). This might be why transfer login tasks often fail with 'unspecified error' message (v. helpful) even though logins have transferred. If anyone can throw any light on that would appreciate it.
---------------------------------------------------------------------
April 16, 2007 at 9:34 am
Thanks all!
Del Piero
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply