还原后的数据库帐户同步

最新推荐文章于 2021-11-30 14:35:02 发布

转载最新推荐文章于 2021-11-30 14:35:02 发布 · 75 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://blog.51cto.com/77719008/477869

本文提供两个SQL Server存储过程，用于实现服务器间用户信息的同步。一是将原服务器的用户信息生成语句并复制到新服务器执行，完成用户同步；二是针对已还原数据库的用户同步需求。

-------------将原服务器上的用户信息生成语句，复制到新服务器上执行，即可将新旧服务器用户同步

exec sp_help_revlogin

CREATE PROCEDURE sp_help_revlogin
        @login_name sysname = NULL
AS
DECLARE @name    sysname,@dbname sysname
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
   -- SELECT a.sid, a.name as login, password,b.name as dbname FROM master..sysxlogins a join master..sysdatabases b
select sid,name,password_hash,default_database_name from sys.sql_logins
    WHERE name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
select sid,name,password_hash,default_database_name from sys.sql_logins
    WHERE name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @binpwd,@dbname
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 ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr

      IF (@binpwd IS NOT NULL)
      BEGIN -- Non-null password
        EXEC sp_hexadecimal @binpwd, @txtpwd OUT
       -- IF (@xstatus & 2048) = 2048
         -- SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'

          SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
        PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
          + ''', @pwd, @sid = ' + @SID_string + ',@defdb = '''+@dbname+''', @encryptopt = '
      END
      ELSE
BEGIN
        -- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
          + ''', NULL, @sid = ' + @SID_string + ',@defdb = '''+@dbname+''', @encryptopt = '
      END

        SET @tmpstr = @tmpstr + '''skip_encryption'''
      PRINT @tmpstr

END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @binpwd,@dbname
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0

--------------对已经还原的数据库进行用户同步的存储过程
GO
create procedure up_syn_dbuser_logins
@dbname sysname=null
/*
返回值解释 :
1,成功返回值:0
2,失败返回值:-1
--对windows用户与SQL Server登陆用户重名的，此存储过程会报错，sp_change_users_login报错。
Example:
exec bdg.dbo.sp_change_users_login 'report'
exec system.dbo.up_syn_dbuser_logins 'bdg'
*/
as
set nocount on

if @dbname is null
begin
set @dbname='['+DB_NAME()+']'
end
else
begin
set @dbname='['+DB_NAME(DB_ID(@dbname))+']'
end
if @dbname is null
begin
raiserror 50000 'The database is not exist! Please check the DB name! '
return -9
end

declare @message nvarchar(4000)
declare @userstring nvarchar(4000)
declare @i int
declare @return int

declare @ntext nvarchar(4000)
set @ntext=N'
declare @userName sysname
declare @LoginName sysname
declare @cursor cursor

set @cursor= cursor forward_only static for
select a.name,b.name from '+@dbname+'..sysusers a join master..syslogins b
on a.name=b.name
where a.sid<>b.sid
and a.hasdbaccess=1
and a.islogin=1

open @cursor

fetch next from @cursor into @userName,@loginname

set @i=0
set @userstring=''''

while @@fetch_status=0
begin

exec '+@dbname+'.dbo.sp_change_users_login ''Update_One'', @userName,@loginname

select @return=@@error
if @return<>0
begin
set @message=''存储过程up_syn_dbuser_logins同步帐户失败，帐户是：''+@userName+ CHAR(13)
+ ''此次操作成功同步了''+ltrim(str(@i))+''个帐户：''+ CHAR(13) + @userstring
break
end

set @userstring=@userstring + @userName+ CHAR(13)
set @i=@i+1
fetch next from @cursor into @userName,@loginname
end
'
exec sp_executesql @ntext ,N'@i int output,@userstring nvarchar(4000) output,@message nvarchar(4000) output,@return int output',
@i output,@userstring output,@message output,@return output

if @return<>0
begin
raiserror 50000 @message
return -1
end
else
begin
print '祝贺！数据库：'+@dbname+'帐户同步成功！'+ CHAR(13) +
   '此次操作成功同步了'+ltrim(str(@i))+'个帐户：'+ CHAR(13) +@userstring
return 0
end