What is the best procedure to move database logins between servers ?
DTS logins over? Should i move the logins first then the user databases or is it the other way around ?Not sure of the best way...
Last time I did it, I did the following;
Create a blank db of same name on destination Server.
Used the transfer logins DTS
Detached the blank DB
Attached the Production DB
Ran the sp_change_users_login for each transfered login.
This worked a treat, however you need to run the SP for each user which could prove a pita if you have many logins.
:@.)|||Install these two scripts on the source database and EXEC sp_help_revlogin. You will then have a script to copy and run on the destination server. It will transfer the SIDs for you so you don't have to do any synching with sp_change_user_login.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.sp_help_revlogin Script Date: 6/21/2004 10:04:05 PM ******/
ALTER PROCEDURE sp_help_revlogin @.login_name
sysname = NULL
AS
--Declare needed variables.
DECLARE
@.name sysname,
@.xstatus int,
@.binpwd varbinary (256),
@.txtpwd sysname,
@.tmpstr varchar (256),
@.SID_varbinary varbinary(85),
@.SID_string varchar(256),
@.dbname varchar(255)
--Determine whether to process one login or all. Set up cursor accordingly.
IF (@.login_name IS NULL)
BEGIN
DECLARE login_curs CURSOR FOR
SELECT
sxl.sid,
sxl.name,
sxl.xstatus,
sxl.password,
sd.name AS dbname
FROM
master..sysxlogins sxl
INNER JOIN master..sysdatabases sd ON sxl.dbid = sd.dbid
WHERE
sxl.srvid IS NULL
AND sxl.name <> 'sa'
END
ELSE
BEGIN
DECLARE login_curs CURSOR FOR
SELECT
sxl.sid,
sxl.name,
sxl.xstatus,
sxl.password,
sd.name AS dbname
FROM
master..sysxlogins sxl
INNER JOIN master..sysdatabases sd ON sxl.dbid = sd.dbid
WHERE
sxl.srvid IS NULL
AND sxl.name <> @.login_name
END
OPEN login_curs
FETCH NEXT FROM login_curs
INTO
@.SID_varbinary,
@.name,
@.xstatus,
@.binpwd,
@.dbname
--If no logins found, exit the procedure.
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SELECT @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SELECT @.tmpstr =
'** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE
BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE
BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
BEGIN
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
END
ELSE
BEGIN
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
END
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr =
'EXEC master..sp_addlogin ''' + @.name + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE
BEGIN
BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr =
'EXEC master..sp_addlogin ''' + @.name + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
END
IF (@.xstatus & 2048) = 2048
BEGIN
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
PRINT @.tmpstr
END
ELSE
BEGIN
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
--Add the default database.
SET @.tmpstr = 'EXEC master..sp_defaultdb ''' + @.name + ''',''' + @.dbname + ''''
PRINT @.tmpstr
END
FETCH NEXT FROM login_curs
INTO
@.SID_varbinary,
@.name,
@.xstatus,
@.binpwd,
@.dbname
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.sp_hexadecimal Script Date: 6/21/2004 10:05:58 PM ******/
ALTER PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||Dang!
Yeah, forget to mention you have to run an SP on the source...
Details can be found at..
http://support.microsoft.com/default.aspx?kbid=246133#4|||Yes. You install both scripts in the master database on the source server. You execute sp_help_revlogin. You then run the permissions script it gives you on the destination. BTW, this version is a little bit different then the MS version. Be sure to include the sp_hex procedure also.
