I was tasked with migrating a SQL 7 box (yes still heavily used) to SQL 2008. Really wasn’t that difficult, perhaps I’ll make another post about the process and issues I ran into. But for now let me talk about something real quick.
When restoring a database to a new server you may not have your security setup properly. For example, the database will have users that don’t exist on the server. Or perhaps they do but they are not linked properly. This is the issue I ran into.
Sometimes it’s just as easy to delete the users from the database and recreate them on the server to relink. This is fine if you have simple permissions. But if you have a complex set of permissions on a large number of tables, this get’s difficult to recreate properly and of course you always have the potential to create errors in multiple applications that may connect as that user.
Then I found sp_change_users_login, this has saved the day. This allowed me to fix the orphaned users after the restore.
EXEC sp_change_users_login 'Auto_Fix', 'orphaned-user-name'
How do we quickly determine if we have this issue?
EXEC sp_change_users_login 'Report'
This will give us a list of orphaned users. Very handy indeed.