【IT168技术文档】

　　注意：下面的过程取决于 SQL Server 系统表。这些表的结构在 SQL Server 的不同版本之间可能会有变化，请不要直接从系统表中选择。

Begin Script, Create sp_help_revlogin procedure
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 '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 

　　在创建 sp_help_revlogin 存储过程之后，请从源服务器上的查询分析器中运行 sp_help_revlogin 过程。

　　sp_help_revlogin 存储过程可同时用于 SQL Server 7.0 和 SQL Server 2000。sp_help_revlogin 存储过程的输出是登录脚本，该脚本可创建带有原始 SID 和密码的登录。保存输出，然后将其粘贴到目标 SQL Server 上的查询分析器中，并运行它。