深藍色論壇

在 SQL Server 中將備份檔 copy 至別台主機 restore 時, 會發生 user 對應 logins 不上的狀況, 如何解決這個問題, 我們可以利用 sp_change_users_login 這個 sp 來將其做對應, 但如何一次大量作業呢? 我們可以利用列舉使用者資料庫, 再配合列舉使用者, 配合 sp_change_users_login 的 auto_fix action 來進行操作, 如此一來即可在所有的資料庫中將有問題的 user , login 的 mapping 進行修正!

代碼: 選擇全部

     
    declare @dbname nvarchar(200), @cmd2 nvarchar(2000) 
    declare c_db cursor for 
      select name from sysdatabases where dbid > 4 
    open c_db 
     
    fetch next from c_db into @dbname 
    while @@fetch_status=0 
    begin 
      print @dbname 
      set @cmd2='use '+@dbname + ' ' +  
    ' 
    declare @name nvarchar(200), @cmd nvarchar(200)  
    declare c_user cursor  for  
      select name from sysusers where altuid=0 and createdate > ''2001/1/1'' 
    open c_user  
      
    fetch next from c_user into @name 
    while @@fetch_status=0  
    begin  
      print @name      
      exec sp_change_users_login ''auto_fix'', @name  
      fetch next from c_user into @name 
      
    end  
     
    close c_user  
    deallocate c_user 
     
    ' 
      exec (@cmd2) 
      fetch next from c_db into @dbname 
    end 
    close c_db 
    deallocate c_db

修改成以下的會主動過瀘不除在的帳號...

代碼: 選擇全部

declare @dbname nvarchar(200), @cmd2 nvarchar(2000)

declare c_db cursor for
 select name from sysdatabases where dbid > 4
open c_db
fetch next from c_db into @dbname
while @@fetch_status=0
begin  
 print @dbname
 set @cmd2='use '+@dbname +
'
 declare @name nvarchar(200), @cmd nvarchar(200)  
 declare c_user cursor  for
   select a.name from sysusers a, master..syslogins b where a.name = b.name and a.altuid=0 and a.createdate > ''2001/1/1''
 open c_user
 fetch next from c_user into @name
 while @@fetch_status=0
 begin  
   print @name
   exec sp_change_users_login ''auto_fix'',  @name
   fetch next from c_user into @name  
 end
 close c_user
 deallocate c_user  
'
 select @cmd2
 exec(@cmd2)
 fetch next from c_db into @dbname
end
close c_db
deallocate c_db

在 sql 2005 中, 必需再調整一下, 因為原來的 altuid=0 已不足夠做為條件, 目前測試可用的查詢方式為:

select name from sysusers where issqluser=1 and name not in ('dbo', 'guest', 'INformATION_SCHEMA', 'sys')

如此修改程序為:

代碼: 選擇全部

declare @dbname nvarchar(200), @cmd2 nvarchar(2000)
declare c_db cursor for
 select name from sysdatabases where dbid > 4
open c_db

fetch next from c_db into @dbname
while @@fetch_status=0
begin
 print @dbname
 set @cmd2='use '+@dbname + ' ' +
'
declare @name nvarchar(200), @cmd nvarchar(200)
declare c_user cursor  for  
 select name from sysusers where issqluser=1 and name not in (''dbo'', ''guest'', ''INformATION_SCHEMA'', ''sys'')
open c_user

fetch next from c_user into @name
while @@fetch_status=0
begin
 print @name    
 exec sp_change_users_login ''auto_fix'', @name
 fetch next from c_user into @name

end

close c_user
deallocate c_user

'
 exec (@cmd2)
 fetch next from c_db into @dbname
end
close c_db
deallocate c_db

深藍色論壇

[MSSQL]將restore到不同主機的database中的user對應主機的logins

[MSSQL]將restore到不同主機的database中的user對應主機的logins