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
No Comments
