Logins mappings to Server Roles did not migrate

Last Post 06 Jul 2004 01:14 PM by talltop. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
bigelectricmac
New Member
New Member

--
06 Jul 2004 12:59 PM
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 ?


Thanks

talltop
New Member
New Member

--
06 Jul 2004 01: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
GO
CREATE 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

--------------------------------------------------------------------------------------------


/*-----------------------------------------------------------------------------*
| 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
--
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! --
------------------------------------------------------------------------------------------------

DECLARE login_curs CURSOR FOR
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)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEAL
You are not authorized to post a reply.

Acceptable Use Policy