Get error: The database is not accessible. (ObjectExplorer)

[smsmail]

Hello

Thanks in advance for your help.

When trying to access the sql server database thru SSMS, I am getting the following message "The database SMSProductionSQL is not accessible. (ObjectExplorer)". Yet, I am able to INSERT/UPDATE/DELETE the database using an ACCESS frontend application.

I am new to SQL Administration, and am at a lost. What could possibly cause this problem? Where should I look first? I am using SQL Server 2008 Express.

Again, thanks for your help.

 

[SQLBill]

Access is probably using an application login (you log into Access, execute the command, and Access passes the information to the database using a different login/password).

When you try to access the database, you are using your own login/password. You might not have permission to access the database directly.

Are you the Database Administrator? If not, request the DBA check your permissions on the database.

If you are, can you see the SQL Server error logs? If so, check for login errors.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[smsmail]

SQLBill, thanks so much for your help.

I have looked at the error log and it appears that I do not have any login errors. The log message when accessing the SMSProductionSQL database using SSMS is: 2011-06-16 16:12:55.70 spid55 Starting up database 'SMSProductionSQL'.

Any thoughts?

 

[SQLBill]

Is the database a large database? If so, when you initially try to connect to it, you may not be giving it enough time to start up.

Check this.....right click on the database, select Properties. Go to Options. Find Auto Close...it probably says True. That means everytime there aren't connections to the database, the database is closed (not accessible). When a new connection is make, the database has to be started up again...the larger the database, the longer it takes. I suggest changing that value to False and seeing if that resolves your issue.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[smsmail]

Thanks again SQLBill, I am really at a loss. The database is not large.

Following your directions, I selected the database, select properties and I got the following error: Cannot show requested dialog. (SqlMgmt)Property Collation is not available for Database '[SMSProductionSQL]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

In the past I have been able to access the database thru SSMS, and performed many different task. The last task I performed was granting user privileges. Now, I cannot access the database.

Why is it that now, I have "insufficent access rights"?

Again, thanks for your help!

 

[SQLBill]

Are you the database administrator? If not it's possible your permissions have been changed.

If you are the database administrator, can you try logging in using the SA account and then finding your login (Security>Logins) and checking what permissions you have for the database.

It's possible permissions have been revoked/denied for you. Maybe you were accessing the database under BUILTIN\Administrator and that login was removed.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875