August 8, 2012 at 11:36 pm
Hi All,
I got an error after executing the script.
Error:
-----
Msg 208, Level 16, State 1, Procedure sp_help_revlogin, Line 11
Invalid object name 'master..sysxlogins'.
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
----- End Script -----
EXEC master..sp_help_revlogin
GO
Pradeep
August 9, 2012 at 12:10 am
You are running the wrong script (belongs to SQL 2000).
Get the right version for SQL 2005 from here http://support.microsoft.com/kb/918992
August 9, 2012 at 12:27 am
Thanks suresh,
Successfully executed the script and also executed the "sp_help_revlogin" but database roles not affected
Is it having any scripts to transfer roles also.
Pradeep
August 9, 2012 at 12:43 am
Right click on database ->Tasks -> Generate Scripts....
and select Database roles...
August 9, 2012 at 12:51 am
thanks suresh, but we have around 600 databases in one particular instance.
So database by database right click and generate the script is very painful work.
Is it having any scripts along with transfer login and password.
It will great helpful for me...
Pradeep
August 9, 2012 at 1:05 am
I have not come across script to transfer roles.
Others might pitch in
August 9, 2012 at 1:07 am
Thanks suresh...
Pradeep
August 9, 2012 at 1:48 am
Just now I wrote this script. Please test it on test machine before running on production:
SET NOCOUNT ON
GO
EXEC sp_MSforeachdb 'USE [?]
select ''CREATE ROLE ['' + a.name + ''] AUTHORIZATION ['' + b.name + '']
GO''
from sys.database_principals a
inner join sys.database_principals b
on a.owning_principal_id = b.principal_id
where a.type_desc = ''DATABASE_ROLE''
and a.is_fixed_role = 0
and a.name <> ''public''
'
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply