/* Sp_help_revlogin script
** Generated 06 24 2009 on WORKGROU-B1XTVC */
DECLARE @ pwd sysname
-- Login: hxtest
SET @ pwd = CONVERT (varbinary (256), CONVERT)
EXEC master .. sp_addlogin '20wa', @ pwd, @ sid = 0x1738BB6AD0CD24498F67FB5589E8EDCB, @ encryptopt = 'skip _ encryption'
......
Step 2
SP_DEFAULTDB 'cyiyun ', 'db _ wayup'
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
DECLARE @ name sysname
DECLARE @ xstatus int
DECLARE @ binpwd varbinary (256)
DECLARE @ txtpwd sysname
DECLARE @ tmps tutorial tr varchar (256)
DECLARE @ SID_varbinary varbinary (85)
DECLARE @ SID_string varchar (256)
IF (@ login_name is null)
DECLARE login_curs CURSOR
SELECT sid, name, xstatus, password FROM master .. sysxlogins
WHERE srvid is null and name <> 'sa'
ELSE
DECLARE login_curs CURSOR
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 'Clare @ 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