SQL Server Alwayson 主从数据库账号同步

概述我们建立了Alwayson后，辅助副本下的数据库是没有相应的账号的，怎么样进行账号的同步呢？怎么在不知道密码的情况下，进行账号的同步设置。 我们可以通过SP--sp_help_revlogin 来实现

我们建立了Alwayson后，辅助副本下的数据库是没有相应的账号的，怎么样进行账号的同步呢？怎么在不知道密码的情况下，进行账号的同步设置。

 

我们可以通过SP--sp_help_revlogin 来实现，此存储过程在主副本上创建了，在执行的时候直接数据你需要同步的账号就会生成创建的sql命令。

我们将这个sql 命令 copy至辅助副本上去执行，然后辅助副本上关于这个账号就生效了。

 

 

SP-- sp_help_revlogin的完整代码如下（需先创建sp_hexadecimal，代码随后）

USE [master]GO/****** Object:  StoredProcedure [dbo].[sp_help_revlogin]    Script Date: 2016/12/9 16:21:57 ******/SET ANSI_NulLS ONGOSET QUOTED_IDENTIFIER ONGOCreate PROCEDURE [dbo].[sp_help_revlogin] @login_name sysname = NulL ASDECLARE @name sysnameDECLARE @type varchar (1)DECLARE @hasaccess intDECLARE @denylogin intDECLARE @is_Disabled intDECLARE @PWD_varbinary  varbinary (256)DECLARE @PWD_string  varchar (514)DECLARE @SID_varbinary varbinary (85)DECLARE @SID_string varchar (514)DECLARE @tmpstr  varchar (1024)DECLARE @is_policy_checked varchar (3)DECLARE @is_expiration_checked varchar (3)DECLARE @defaultdb sysname IF (@login_name IS NulL)  DECLARE login_curs CURSOR FOR      SELECT p.sID,p.name,p.type,p.is_Disabled,p.default_database_name,l.hasaccess,l.denylogin FROM sys.server_principals p left JOIN sys.syslogins l      ON ( l.name = p.name ) WHERE p.type IN ( 'S','G','U' ) AND p.name <> 'sa'ELSE  DECLARE login_curs CURSOR FOR      SELECT p.sID,'U' ) AND p.name = @login_nameOPEN login_cursFETCH NEXT FROM login_curs INTO @SID_varbinary,@name,@type,@is_Disabled,@defaultdb,@hasaccess,@denyloginIF (@@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 ''WHILE (@@fetch_status <> -1)BEGIN  IF (@@fetch_status <> -2)  BEGIN    PRINT ''    SET @tmpstr = '-- Login: ' + @name    PRINT @tmpstr    IF (@type IN ( 'G','U'))    BEGIN -- NT authenticated account/group      SET @tmpstr = 'CREATE LOGIN ' + QUOTEname( @name ) + ' FROM windows WITH DEFAulT_DATABASE = [' + @defaultdb + ']'    END    ELSE BEGIN -- sql Server authentication        -- obtain password and sID            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name,'PasswordHash' ) AS varbinary (256) )        EXEC sp_hexadecimal @PWD_varbinary,@PWD_string OUT        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT         -- obtain password policy state        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NulL END FROM sys.sql_logins WHERE name = @name        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NulL END FROM sys.sql_logins WHERE name = @name             SET @tmpstr = 'CREATE LOGIN ' + QUOTEname( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED,SID = ' + @SID_string + ',DEFAulT_DATABASE = [' + @defaultdb + ']'        IF ( @is_policy_checked IS NOT NulL )        BEGIN          SET @tmpstr = @tmpstr + ',CHECK_POliCY = ' + @is_policy_checked        END        IF ( @is_expiration_checked IS NOT NulL )        BEGIN          SET @tmpstr = @tmpstr + ',CHECK_EXPIRATION = ' + @is_expiration_checked        END    END    IF (@denylogin = 1)    BEGIN -- login is denIEd access      SET @tmpstr = @tmpstr + '; DENY CONNECT sql TO ' + QUOTEname( @name )    END    ELSE IF (@hasaccess = 0)    BEGIN -- login exists but does not have access      SET @tmpstr = @tmpstr + '; REVOKE CONNECT sql TO ' + QUOTEname( @name )    END    IF (@is_Disabled = 1)    BEGIN -- login is Disabled      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTEname( @name ) + ' disABLE'    END    PRINT @tmpstr  END  FETCH NEXT FROM login_curs INTO @SID_varbinary,@denylogin   ENDCLOSE login_cursDEALLOCATE login_cursRETURN 0

　　

注意其上的SP在代码中会包含sp --sp_hexadecimal，需要先创建

USE [master]GO/****** Object:  StoredProcedure [dbo].[sp_hexadecimal]    Script Date: 2016/12/9 16:11:25 ******/SET ANSI_NulLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[sp_hexadecimal]    @binvalue varbinary(256),@hexvalue varchar (514) OUTPUTASDECLARE @charvalue varchar (514)DECLARE @i intDECLARE @length intDECLARE @hexstring char(16)SELECT @charvalue = '0x'SELECT @i = 1SELECT @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 + 1ENDSELECT @hexvalue = @charvalueGO

　　

总结

以上是内存溢出为你收集整理的SQL Server Alwayson 主从数据库账号同步全部内容，希望文章能够帮你解决SQL Server Alwayson 主从数据库账号同步所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。