本篇内容主要讲解“怎么编写SQLSRVER同步ID脚本”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么编写SQLSRVER同步ID脚本”吧!

USE[master]GO/******Object:StoredProcedure[dbo].[sp_hexadecimal]ScriptDate:2018/8/910:48:17******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATEPROCEDURE[dbo].[sp_hexadecimal]@binvaluevarbinary(256),@hexvaluevarchar(514)OUTPUTASDECLARE@charvaluevarchar(514)DECLARE@iintDECLARE@lengthintDECLARE@hexstringchar(16)SELECT@charvalue='0x'SELECT@i=1SELECT@length=DATALENGTH(@binvalue)SELECT@hexstring='0123456789ABCDEF'WHILE(@i<=@length)BEGINDECLARE@tempintintDECLARE@firstintintDECLARE@secondintintSELECT@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+1ENDSELECT@hexvalue=@charvalueGO

USE[master]GO/******Object:StoredProcedure[dbo].[sp_help_revlogin]ScriptDate:2018/8/910:48:23******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATEPROCEDURE[dbo].[sp_help_revlogin]@login_namesysname=NULLASDECLARE@namesysnameDECLARE@typevarchar(1)DECLARE@hasaccessintDECLARE@denyloginintDECLARE@is_disabledintDECLARE@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@defaultdbsysnameIF(@login_nameISNULL)DECLARElogin_cursCURSORFORSELECTp.sid,p.name,p.type,p.is_disabled,p.default_database_name,l.hasaccess,l.denyloginFROMsys.server_principalspLEFTJOINsys.sysloginslON(l.name=p.name)WHEREp.typeIN('S','G','U')ANDp.name<>'sa'ELSEDECLARElogin_cursCURSORFORSELECTp.sid,p.name,p.type,p.is_disabled,p.default_database_name,l.hasaccess,l.denyloginFROMsys.server_principalspLEFTJOINsys.sysloginslON(l.name=p.name)WHEREp.typeIN('S','G','U')ANDp.name=@login_nameOPENlogin_cursFETCHNEXTFROMlogin_cursINTO@SID_varbinary,@name,@type,@is_disabled,@defaultdb,@hasaccess,@denyloginIF(@@fetch_status=-1)BEGINPRINT'Nologin(s)found.'CLOSElogin_cursDEALLOCATElogin_cursRETURN-1ENDSET@tmpstr='/*sp_help_revloginscript.'PRINT@tmpstrSET@tmpstr='**Generated'+CONVERT(varchar,GETDATE())+'on'+@@SERVERNAME+'*/'PRINT@tmpstrPRINT''WHILE(@@fetch_status<>-1)BEGINIF(@@fetch_status<>-2)BEGINPRINT''SET@tmpstr='--Login:'+@namePRINT@tmpstrIF(@typeIN('G','U'))BEGIN--NTauthenticatedaccount/groupSET@tmpstr='CREATELOGIN'+QUOTENAME(@name)+'FROMWINDOWSWITHDEFAULT_DATABASE=['+@defaultdb+']'ENDELSEBEGIN--SQLServerauthentication--obtainpasswordandsidSET@PWD_varbinary=CAST(LOGINPROPERTY(@name,'PasswordHash')ASvarbinary(256))EXECsp_hexadecimal@PWD_varbinary,@PWD_stringOUTEXECsp_hexadecimal@SID_varbinary,@SID_stringOUT--obtainpasswordpolicystateSELECT@is_policy_checked=CASEis_policy_checkedWHEN1THEN'ON'WHEN0THEN'OFF'ELSENULLENDFROMsys.sql_loginsWHEREname=@nameSELECT@is_expiration_checked=CASEis_expiration_checkedWHEN1THEN'ON'WHEN0THEN'OFF'ELSENULLENDFROMsys.sql_loginsWHEREname=@nameSET@tmpstr='CREATELOGIN'+QUOTENAME(@name)+'WITHPASSWORD='+@PWD_string+'HASHED,SID='+@SID_string+',DEFAULT_DATABASE=['+@defaultdb+']'IF(@is_policy_checkedISNOTNULL)BEGINSET@tmpstr=@tmpstr+',CHECK_POLICY='+@is_policy_checkedENDIF(@is_expiration_checkedISNOTNULL)BEGINSET@tmpstr=@tmpstr+',CHECK_EXPIRATION='+@is_expiration_checkedENDENDIF(@denylogin=1)BEGIN--loginisdeniedaccessSET@tmpstr=@tmpstr+';DENYCONNECTSQLTO'+QUOTENAME(@name)ENDELSEIF(@hasaccess=0)BEGIN--loginexistsbutdoesnothaveaccessSET@tmpstr=@tmpstr+';REVOKECONNECTSQLTO'+QUOTENAME(@name)ENDIF(@is_disabled=1)BEGIN--loginisdisabledSET@tmpstr=@tmpstr+';ALTERLOGIN'+QUOTENAME(@name)+'DISABLE'ENDPRINT@tmpstrENDFETCHNEXTFROMlogin_cursINTO@SID_varbinary,@name,@type,@is_disabled,@defaultdb,@hasaccess,@denyloginENDCLOSElogin_cursDEALLOCATElogin_cursRETURN0GO

到此，相信大家对“怎么编写SQLSRVER同步ID脚本”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！