Re: Converting User SIDS from SQL 2000 to SQL 2005
- From: "Uri Dimant" <urid@xxxxxxxxxxx>
- Date: Thu, 9 Feb 2006 15:37:31 +0200
John
I did it by using two stored procedures that MS have provided. I have not
played with it on SQL Server 2005 since I re-created logins on the new
server , however it worth to try.
USE master
GO
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
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @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
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
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
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"John Beschler" <JohnBeschler@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8F5D93F3-5E94-46C7-ACA1-5B85B907E099@xxxxxxxxxxxxxxxx
WE have a query that we use whenever we transfer a database between
servers
that resyns the SIDS in the database to match those in the new
installation.
I am trying to convert a databse from 2000 to run on 2005 and the query no
longer works because 2005 does not store the user information in the same
way
as 2000. Can someone point me in the right direction to update this query
for 2005. Or, perhaps there is another approach I should be taking?
Here is the query:
/*********************************************************
* This script is used to synch the SIDs between
* the login and the database user. This should
* be executed after a database is copied to a new
* server and attached to keep the permissions as
* they were on the server where the database originated.
**********************************************************/
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
DECLARE @LoginSID VARBINARY(85)
-- Update the SID for the SEMS user.
SELECT @LoginSID = sid
FROM master..sysxlogins
WHERE name = 'SEMS'
UPDATE sysusers
SET sid = @LoginSID
WHERE name = 'SEMS'
-- Update the SID for the RptWriters user.
SELECT @LoginSID = sid
FROM master..sysxlogins
WHERE name = 'RptWriters'
UPDATE sysusers
SET sid = @LoginSID
WHERE name = 'RptWriters'
-- Update the SID for the TSEQALS user.
SELECT @LoginSID = sid
FROM master..sysxlogins
WHERE name = 'TSEQALS'
UPDATE sysusers
SET sid = @LoginSID
WHERE name = 'TSEQALS'
GO
sp_configure 'allow updates', 0
GO
RECONFIGURE
GO
Thanks,
John
.
- Follow-Ups:
- Re: Converting User SIDS from SQL 2000 to SQL 2005
- From: John Beschler
- Re: Converting User SIDS from SQL 2000 to SQL 2005
- Prev by Date: Re: Configure "Remote Connections" via Group Policy?
- Next by Date: Re: User Instance-like SQL Server Express 2005 setup
- Previous by thread: In TSQL how to use OPENXML to open an ntext field in a table? I can not read the ntext into a local var as TSQL does not allowe ntext local variables.
- Next by thread: Re: Converting User SIDS from SQL 2000 to SQL 2005
- Index(es):
Relevant Pages
|
|
