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

GO

/*

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

 

 

 

 

 

--------------对已经还原的数据库进行用户同步的存储过程
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 

GO