Converting User SIDS from SQL 2000 to SQL 2005

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 wa
y
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,
JohnJohn
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@.discussions.microsoft.com> wrote in message
news:8F5D93F3-5E94-46C7-ACA1-5B85B907E099@.microsoft.com...
> 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
>|||Uri,
Thanks for your suggestion; however, apparently, the sysxlogins table no
longer exists in 2005. That's the point my script was failing as well.
Thanks,
"Uri Dimant" wrote:

> 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@.discussions.microsoft.com> wrote in message
> news:8F5D93F3-5E94-46C7-ACA1-5B85B907E099@.microsoft.com...
>
>|||Hi John.
Take a look at this link http://support.microsoft.com/kb/246133/en-us. I
change this script to generate new DDL sintax for SQL Server 2005. You can
get it from
http://solidqualitylearning.com/blo...01/28/1515.aspx
(comments are in Spanish, sorry :-) )
"John Beschler" <JohnBeschler@.discussions.microsoft.com> escribi en el
mensaje news:D002F332-27CD-422A-B26C-BA267F5C77C3@.microsoft.com...[vbcol=seagreen]
> Uri,
> Thanks for your suggestion; however, apparently, the sysxlogins table no
> longer exists in 2005. That's the point my script was failing as well.
> Thanks,
>
> "Uri Dimant" wrote:
>