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