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

我们建立了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 on
go
set quoted_identifier on
go
create procedure [dbo].[sp_help_revlogin] @login_name sysname = null as
declare @name sysname
declare @type varchar (1)
declare @hasaccess int
declare @denylogin int
declare @is_disabled int
declare @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, 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 = @login_name
open login_curs

fetch next from login_curs into @sid_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
if (@@fetch_status = -1)
begin
  print 'no login(s) found.'
  close login_curs
  deallocate login_curs
  return -1
end
set @tmpstr = '/* sp_help_revlogin script '
print @tmpstr
set @tmpstr = '** generated ' + convert (varchar, getdate()) + ' on ' + @@servername + ' */'
print @tmpstr
print ''
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, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   end
close login_curs
deallocate login_curs
return 0

　　

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

use [master]
go

/****** object:  storedprocedure [dbo].[sp_hexadecimal]    script date: 2016/12/9 16:11:25 ******/
set ansi_nulls on
go

set quoted_identifier on
go

create procedure [dbo].[sp_hexadecimal]
    @binvalue varbinary(256),
    @hexvalue varchar (514) output
as
declare @charvalue varchar (514)
declare @i int
declare @length int
declare @hexstring char(16)
select @charvalue = '0x'
select @i = 1
select @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 + 1
end

select @hexvalue = @charvalue

go