3

I'm developing an ASP.NET MVC3 web app, which connects to a SQL Server 2008 R2 database.

Everything was working fine, until I started getting this error:

The database myDb is not accessible. (ObjectExplorer)

This issue appears also when I try and browse the database in SQL Server Management Studio. It seems I have somehow lost permission to access my database? I can browse other databases just fine.

I am using mixed mode authentication (up until now Windows Authentication has been working fine). If I login as sa I get the same error.

How can I give the required permissions back to my user account, if I cannot access the database?

What sort of things might have caused this to happen in the first place?

The only changes I have made recently have not been database related at all, I'm struggling to figure out what has caused this.

Update Well, after restarting my computer, it all started working again :/ I didn't change anything, hadn't read the replies here yet. Very odd!

4 Answers 4

3

In the past I have found that this has been due to the DB owner being invild, like it was deleted from the SQL instance.

Try this from a query window:

use <database with issue>;
exec sp_changedbowner 'sa';

It should then allow you to browse the DB in the SSMS explorer.

If you are aren't using mixed mode authentication then sub out 'sa' with a domain account, preferably one that serves as the service account with SA rights.

1

Update Well, after restarting my computer, it all started working again :/ I didn't change anything, hadn't read the replies here yet. Very odd!

0

Looks like a very generic error message. Try running the below from windows authenticated user and see if you get any results back.

EXEC sp_change_users_login 'report'

If yes then run below.

EXEC sp_change_users_login 'auto_fix', 'username_from_above'

If this doesn't help, we will try to dig something else.

0

The accepted answer says that it all started working after a reboot, but why?

This happened to me after I tried updating the database with SSDT (Visual Studio plugin), and the script failed with an error. I was unable then to access the database even from SSMS (Management Studio). My database entry showed "(Single User)" next to it, which was a clue to the problem.

The script had set the database into SINGLE_USER mode and terminated abruptly before setting it back to MULTI_USER. After that, the database viewed the SSDT user as still being active and wouldn't allow any other connection. Rebooting cleared that connection (cycling the SQL Server service probably would've done it too), allowing me to reconnect.

From there, I could put the database back into MULTI_USER mode using the DB properties (Options -> State -> Restrict Access), but instead I ran SSDT from a different source branch which ran to completion, resetting the mode.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .