Restore MS SQL database on separate server
So in the midst of doing server migration, I was having a problem with a restored database giving me "permission denied" errors on the new SQL 2005 server I set up. It turns out the problem was due to orphaned users, where the users' old unique identifier (called a security identifier or SID) would not match the new server's even though the user names are equivalent.
To fix that in MSSQL 2000 and above, you first should determine the orphaned users by issuing the following query:
Once you have the usernames, use the following query to reconcile the SIDs:
Note that this will only work if you have the same named users on the destination server.
To fix that in MSSQL 2000 and above, you first should determine the orphaned users by issuing the following query:
exec sp_change_users_login 'report'
Once you have the usernames, use the following query to reconcile the SIDs:
exec sp_change_users_login 'auto_fix', 'username'
Note that this will only work if you have the same named users on the destination server.
| Permalink | E-mail this post 
