Logins mappings to Server Roles did not migrate

Last Post 06 Jul 2004 12:14 PM by talltop. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

06 Jul 2004 11:59 AM
When I migrated SQL Server 7.0 to 2000, I created a DTS package using Transfer Login Task. All logins migrated successfully, but their memberships of server roles did not. That DTS task does not have any options to setup for this.

Is there any way to change DTS package to move their mappings to server roles as well ? Or should I use sp_addsrvrolemember procedure to re-map them ?


New Member
New Member

06 Jul 2004 12:14 PM
Yes, you could do that but in the future you could run this script by Bruce Canaday that does everything at one time(Logins, Fixed server roles, encrypted passwords)

-- Create sp_hexadecimal, used by DR_Script_Logins below -----------------------------------

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
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)
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
SELECT @hexvalue = @charvalue


| Name: DR_Script_Logins.sql
| Author: Bruce Canaday
| Date: 08/09/2002
|----------------------------------------------------------------------------- |
| Purpose: Generate a script to add all logins for a server
| Modified: 10/20/2003 - Add Fixed Server Roles

DECLARE @login_name sysname
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @dbnm sysname
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (256)
DECLARE @loopCnt int

-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! --
-- >>>>>>>>>>>> IF master has been restored with an alternate name change below, <<<<<<<<<<<<
-- e.g. restoring multiple servers onto one
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! --

SELECT l.sid, l.name, xstatus, password, d.name
FROM master..sysxlogins l -- <<<<<<<<<<<<<<<<<< change here
JOIN master..sysdatabases d on d.dbid = l.dbid -- <<<<<<<<<<<<<<<<<< change here
WHERE srvid IS NULL AND l.name <> 'sa'
-- uncomment if master restored with an alternate name and to exclude existing logins
-- AND NOT EXISTS (SELECT 1 FROM master..sysxlogins where master..sysxlogins.name = l.name)

OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dbnm

IF (@@fetch_status = -1)
PRINT 'No login(s) found.'
CLOSE login_curs

Acceptable Use Policy