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

[alwayson数据库未同步]SQLServerAlwayson主从数据库账号同步

原文：https://www.cnblogs.com/sqlserver-mysql/p/12702880.html