我们建立了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
需要了解更多数据库技术:SQL Server Alwayson 主从数据库账号同步，都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集，不代表计算机技术网立场，如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址：https://www.ctvol.com/dtteaching/626203.html