法甲

SQLServer实例之间传输登录和密码

2019-06-19 20:55:36来源:励志吧0次阅读

  ----- Begin Script, Create sp_help_revlogin procedure -----USE masterGOIF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimalGOCREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar(256) OUTPUTASDECLARE @charvalue varchar(256)DECLARE @i intDECLARE @length intDECLARE @hexstring char(16)SELECT @charvalue = '0x'SELECT @i = 1SELECT @length = DATALENGTH (@binvalue)SELECT @hexstring = 'ABCDEF' 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 + 1ENDSELECT @hexvalue = @charvalueGOIF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GOCREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL ASDECLARE @name sysnameDECLARE @xstatus intDECLARE @binpwd varbinary (256)DECLARE @txtpwd sysnameDECLARE @tmpstr varchar (256)DECLARE @SID_varbinary varbinary(85)DECLARE @SID_string varchar(256)IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT sid, name, xstatus, password FROM master..sysxlogins WHERE srvid IS NULL AND name 'sa'ELSE DECLARE login_curs CURSOR FOR SELECT sid, name, xstatus, password FROM master..sysxlogins WHERE srvid IS NULL AND name = @login_nameOPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwdIF (@@fetch_status = -1)BEGIN PRINT 'No login(s) found.' CLOSE login_curs DEALLOCATE login_curs RETURN -1ENDSET @tmpstr = '/* sp_help_revlogin script ' PRINT @tmpstrSET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'PRINT @tmpstrPRINT ''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 @tmpstrEXEC sp_hexadecimal @SID_varbinary,@SID_string OUT SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = ' END ELSE BEGIN -- Null passwordEXEC 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 ENDCLOSE login_curs DEALLOCATE login_curs RETURN 0GO ----- End Script -----

小儿便秘的几个原因
儿童消化不良腹胀怎么办
小儿便秘的饮食禁忌
分享到: