This is a follow up posting on

Issue: Server principal is not able to access the database under the current security context

Here is a script to loop through all of the users and re-attach them to their logins. Note that the assumption here is the logins in the old server/instance exists in the new server/instance

SQL Server 2000/2005 (using sp_change_user_login, which is marked for deprecation):

   1: DECLARE @UserName varchar(35)
   2:  
   3: DECLARE UserListCrs CURSOR FOR
   4: SELECT 
   5:     [name]
   6: FROM 
   7:     sysusers
   8: WHERE 
   9:     issqluser = 1
  10:     AND SUSER_SNAME(sid) IS NULL
  11:     AND [name] <> 'guest'
  12:     -- add additional exceptions here if needed
  13:  
  14: OPEN UserListCrs
  15:  
  16: FETCH NEXT FROM UserListCrs
  17: INTO @UserName
  18:  
  19: -- loop through all users, and remap the user to the login
  20: -- assumption: username is the same as loginname
  21: WHILE @@FETCH_STATUS = 0
  22: BEGIN
  23:     
  24:     EXEC sp_change_users_login 'update_one', @UserName, @UserName  
  25:    
  26:     FETCH NEXT FROM UserListCrs
  27:    INTO @UserName
  28: END     
  29:  
  30: CLOSE UserListCrs
  31: DEALLOCATE UserListCrs

SQL Server 2005 onwards (using ALTER LOGIN, replacement for sp_change_users_login):

   1:  
   2: DECLARE @UserName varchar(35)
   3: DECLARE @SQLString varchar(500)
   4:  
   5: SET @SQLString = ''
   6:  
   7: DECLARE UserListCrs CURSOR FOR
   8: SELECT 
   9:     [name]
  10: FROM 
  11:     sysusers
  12: WHERE 
  13:     issqluser = 1
  14:     AND SUSER_SNAME(sid) IS NULL
  15:     AND [name] <> 'guest'
  16:     -- add additional exceptions here if needed
  17:  
  18: OPEN UserListCrs
  19:  
  20: FETCH NEXT FROM UserListCrs
  21: INTO @UserName
  22:  
  23: -- loop through all users, and remap the user to the login
  24: -- assumption: username is the same as loginname
  25: WHILE @@FETCH_STATUS = 0
  26: BEGIN
  27:     
  28:     SET @SQLString = ' ALTER USER ' + @UserName +
  29:                          ' WITH LOGIN = ' + @UserName
  30:     
  31:     PRINT @SQLString
  32:     -- uncomment below when you are ready to execute
  33:     -- you may also want to rewrite this dynamic SQL using sp_executesql
  34:     -- EXEC(@SQLString)
  35:    
  36:     FETCH NEXT FROM UserListCrs
  37:    INTO @UserName
  38: END     
  39:  
  40: CLOSE UserListCrs
  41: DEALLOCATE UserListCrs
  42:  
  43: -- Sample Output
  44: -- ALTER USER belle WITH LOGIN = belle
  45: 

 

Here is a good reference:

Understanding and dealing with orphaned users in a SQL Server database

http://www.mssqltips.com/tip.asp?tip=1590

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)