Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We are using mixed mode authentication. I can see and qbe uery all of the databases on the server except for one. I am logged in as the SA account. One database does not expand in SSMS when I attempt to look at it and I get the error:

"The database is not accessible (ObjectExplorer)"

When I try to look at it. Error logs are not helpful. The backup also fails.

Any ideas? Could it be corrupt? What are the next steps to bringing this back online?

Thanks!!

share|improve this question

migrated from stackoverflow.com May 6 '11 at 16:05

This question came from our site for professional and enthusiast programmers.

add comment

1 Answer 1

up vote 1 down vote accepted

If you can see the database while logged in as you, but not as SA then it's a permissions issue. The SA must have been restricted from that database. Check with the database administrator. If you're the database administrator, log in as yourself and check SA's permissions.

If you can't see it no matter who is logged in, it could be that it's detached or offline.

Also, this would be better placed on ServerFault.com.

Edit again

Was this database recently restored from a different server? I can't believe i didn't think of this because it happened to me just last month... If this was restored from a backup made on another server, then I have another possible option...

You and I see the login SA as "SA" but under the hood, SQL Server sees it with a different ID. (I think it's a guid).

From time to time, I have to take a backup of DB from our live server and restore it onto our test server. When that happens, the permissions are not set correctly for any SQL Server logins (but they are fine for Windows logins.) I can look at a table, for instance, that shows that SA has access, but when I try to connect using SA, I got the same message.

What we finally figured out was that we needed to DELETE the SA user from the database, then DELETE it at the server level, then set it back up again with the proper permissions.

This is because of the discrepancy between the Id's that SQL Server uses under the hood for the "friendly" username.

For example, on our test server, SA may have an id of abc and on our live server, the id may be def. Somehow this messes things up when restoring a DB from one server to another.

There's an article about it here: http://searchsqlserver.techtarget.com/tip/Restoring-a-database-from-another-SQL-Server

Oops. You have to register for the one above. Here's one you don't need to register for.

http://dbaspot.com/sqlserver-server/359100-database-not-accessible-objectexplorer.html

That last one contains links to a KB that shows a better way to fix this than the way I'd been doing it. That figures. So even if this doesn't answer your problem, you've helped me find a better answer for mine, so +1 to you for asking the question and helping me out.

share|improve this answer
    
Definitely not permissions based. Try running this code and you'll see what I mean: GRANT VIEW ANY DATABASE TO sa. It results in any error (Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.) –  Yuck May 6 '11 at 15:28
1  
If you're logged in as sa and running it, then no you can't. Take a look at the last two words in your error. You can't provide permissions to yourself. That said, I modified my answer because I did think of a coupole of non-permissions possibilities, but I still think this should be on ServerFault.com. –  David Stratton May 6 '11 at 15:34
1  
+1 on moving to ServerFault. A DBA could probably answer this faster than a programmer. –  Yuck May 6 '11 at 15:48
    
David -- you nailed it. The issue was a restore process that had not finished completely and had not cleaned up the SIDs correctly. Sorry it took so long to award the answer! –  John Azzolina May 29 '11 at 11:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.