Tuesday, April 26, 2011

Database Not Accessible

Recently after restoring a database backup which I got from another server I repeatedly encountered an error when logged in using a user who is given permission. But when ‘sa’ account is used I could work with the database without any problem.

The error message was “The database DATABASE NAME is not accessible. (ObjectExplorer)” which was not helpful since it didn’t give any clue to figure out the issue.

image

Later I found that the issue is because the user in the restored database is not properly mapped to the user in the new server and the resolution for this is to run the stored procedure sp_change_users_login to correct the orphaned user.

  • sp_change_users_login 'update_one', 'USER', 'LOGIN' – Links the given user in the current database to the specified login.
  • sp_change_users_login 'auto_fix', 'USER' – Links the given user in the current database to the login having the same name in the current server.

After running this you will be able to access the restored database without any issue using the mentioned database user.

4 comments:

Anonymous said...

unlock iphone
unlock iphone

how to unlock iphone how to unlock iphone unlock iphone
unlock iphone

how to unlock iphone unlock iphone [url=http://ounlockiphone.com]unlock iphone [/url] unlock iphone

Anonymous said...

hello there and thank you for your info – I have definitely picked up anything new from right here. [url=http://www.autoskup.net]Skup samochodów warszawa[/url] I did however expertise a few technical points using this website, since I experienced to reload the web site many times previous to I could get it to load properly. I had been wondering if your hosting is OK? Not that I am complaining, but slow loading instances times will often affect your placement in google and could damage your high quality score if advertising and marketing with Adwords. Well I’m adding this RSS to my e-mail and can look out for a lot more of your respective intriguing content. Make sure you update this again very soon..

Anonymous said...

Had the same problem when I attached an old DB, thanks for pointing me in the right direction on how to solve the login problem. =)

Anonymous said...

This is the second time I've been to your website. Thnx for sharing more details.
My webpage ... SiteClick