USE[master]
GO
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
CreatePROCEDURE[dbo].[sp_help_revlogin]@login_namesysname=NULLAS
DECLARE@namesysname
DECLARE@typevarchar(1)
DECLARE@hasaccessint
DECLARE@denyloginint
DECLARE@is_disabledint
DECLARE@PWD_varbinaryvarbinary(256)
DECLARE@PWD_stringvarchar(514)
DECLARE@SID_varbinaryvarbinary(85)
DECLARE@SID_stringvarchar(514)
DECLARE@tmpstrvarchar(1024)
DECLARE@is_policy_checkedvarchar(3)
DECLARE@is_expiration_checkedvarchar(3)
DECLARE@defaultdbsysname
IF(@login_nameISNULL)
DECLARElogin_cursCURSORFOR
SELECTp.sid,p.name,p.type,p.is_disabled,p.default_database_name,l.hasaccess,l.denyloginFROM
sys.server_principalspLEFTJOINsys.sysloginsl
ON(l.name=p.name)WHEREp.typeIN(‘S‘,‘G‘,‘U‘)ANDp.name‘sa‘
ELSE
DECLARElogin_cursCURSORFOR
SELECTp.sid,p.name,p.type,p.is_disabled,p.default_database_name,l.hasaccess,l.denyloginFROM
sys.server_principalspLEFTJOINsys.sysloginsl
ON(l.name=p.name)WHEREp.typeIN(‘S‘,‘G‘,‘U‘)ANDp.name=@login_name
OPENlogin_curs
FETCHNEXTFROMlogin_cursINTO@SID_varbinary,@name,@type,@is_disabled,@defaultdb,@hasaccess,@denylogin
IF(@@fetch_status=-1)
BEGIN
PRINT‘Nologin(s)found.‘
CLOSElogin_curs
DEALLOCATElogin_curs
RETURN-1
END
SET@tmpstr=‘‘
PRINT@tmpstr
PRINT‘‘
WHILE(@@fetch_status-1)
BEGIN
IF(@@fetch_status-2)
BEGIN
PRINT‘‘
SET@tmpstr=‘--Login:‘+@name
PRINT@tmpstr
IF(@typeIN(‘G‘,‘U‘))
BEGIN--NTauthenticatedaccount/group
SET@tmpstr=‘CREATELOGIN‘+QUOTENAME(@name)+‘FROMWINDOWSWITHDEFAULT_DATABASE=[‘+@defaultdb+‘]‘
END
ELSEBEGIN--SQLServerauthentication
--obtainpasswordandsid
SET@PWD_varbinary=CAST(LOGINPROPERTY(@name,‘PasswordHash‘)ASvarbinary(256))
EXECsp_hexadecimal@PWD_varbinary,@PWD_stringOUT
EXECsp_hexadecimal@SID_varbinary,@SID_stringOUT
--obtainpasswordpolicystate
SELECT@is_policy_checked=CASEis_policy_checkedWHEN1THEN‘ON‘WHEN0THEN‘OFF‘ELSENULLENDFROMsys.sql_loginsWHEREname=@name
SELECT@is_expiration_checked=CASEis_expiration_checkedWHEN1THEN‘ON‘WHEN0THEN‘OFF‘ELSENULLENDFROMsys.sql_loginsWHEREname=@name
SET@tmpstr=‘CREATELOGIN‘+QUOTENAME(@name)+‘WITHPASSWORD=‘+@PWD_string+‘HASHED,SID=‘+@SID_string+‘,DEFAULT_DATABASE=[‘+@defaultdb+‘]‘
IF(@is_policy_checkedISNOTNULL)
BEGIN
SET@tmpstr=@tmpstr+‘,CHECK_POLICY=‘+@is_policy_checked
END
IF(@is_expiration_checkedISNOTNULL)
BEGIN
SET@tmpstr=@tmpstr+‘,CHECK_EXPIRATION=‘+@is_expiration_checked
END
END
IF(@denylogin=1)
BEGIN--loginisdeniedaccess
SET@tmpstr=@tmpstr+‘;DENYCONNECTSQLTO‘+QUOTENAME(@name)
END
ELSEIF(@hasaccess=0)
BEGIN--loginexistsbutdoesnothaveaccess
SET@tmpstr=@tmpstr+‘;REVOKECONNECTSQLTO‘+QUOTENAME(@name)
END
IF(@is_disabled=1)
BEGIN--loginisdisabled
SET@tmpstr=@tmpstr+‘;ALTERLOGIN‘+QUOTENAME(@name)+‘DISABLE‘
END
PRINT@tmpstr
END
FETCHNEXTFROMlogin_cursINTO@SID_varbinary,@name,@type,@is_disabled,@defaultdb,@hasaccess,@denylogin
END
CLOSElogin_curs
DEALLOCATElogin_curs
RETURN0