Mysterious user access errors in an SQL database?

Hi,

Ever experienced some strange error messages like "The table either does not exist or the current user does not have permissions..." when trying to run queries or stored procedures on a database with a user you're sure has access? You check the user's access right to specific objects and everything seems correct? Hmmm? Well if you do, use this simple command on the database in question to see if you have an orphaned user problem:

sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', 

   @LoginName='<login_name>';

If you get some usernames returned then that means you have an orphaned user problem. This can happen sometimes when you backup from one server and restore to another, like your test server. What can happen is that if the SQL logins have different SID then the link between your sql login and your database login is lost.
So, to fix this you run this command on the database:


sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', 
   @LoginName='<login_name>';



And that's how you fix that!