欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-11-25 13:53:38
我们建立了Alwayson后,辅助副本下的数据库是没有相应的账号的,怎么样进行账号的同步呢?怎么在不知道密码的情况下,进行账号的同步设置。 我们可以通过SP--sp_help_revlogin 来实现,此存储过程在主副本上创建了,在执行的时候直接数据你需要同步的账号就会生成创建的SQL命令。 我们将 ......

我们建立了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