Database not listed in SQL Server 2008 Management Studio for user in db_owner role

Leon O'Daniel
Leon O'Daniel used Ask the Experts™
on
I have an issue with a new login that I just created for my SQL Server 2008 database. I added the login on the server, added this login as a user to my SQL Server 2008 database, added this user to the db_owner role, and set this person's default schema as dbo. When I logon with this user's login credentials, the database is not displayed in the Object Explorer. Please help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
what happens if run this query in the master database:
 
USE <yourdbname>
 
does it change the database name or do you get an error?
please run this query and let me know whether your database is listed there or notl

select * from sys.databases
Leon O'DanielVice President

Author

Commented:
Thanks for the quick responses. The database is listed in the query results if I run "select * from sys.databases". If I execute USE dsaa (my database), I get a Command(s) completed successfully message, but I do not get my database listed. To clarify, my database does display properly for 3 other Users that I have created on this database.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

I am 100% sure, that is permission issue. are you using "SA" account? it must show your database, by logging with SA or Administrator account you can map that database to those logins which doesn't show you database

Commented:
Try this:

USE master;
GRANT VIEW ANY DATABASE TO YourLogin;

Commented:
It sounds like at some point someone's done a "DENY VIEW ANY DATABASE TO ..." command. REVOKE might help too, if you're not keen on GRANT.

Rob

Commented:
@rob_farley: I think REVOKE will not help here if DENY was performed. BTW, isn't VIEW ANY DATABASE granted by default to the public role?

Commented:
Actually a GRANT won't help if the login (has to be the login we're talking about) is in a group that has specifically been denied.

Try check sys.all_permissions to have a look around.

Rob

Commented:
@rob_farley: Right. Then we should look for DENY in sys.server_permissions:

select pr.name
from sys.server_permissions p
inner join sys.server_principals pr on pr.principal_id = p.grantee_principal_id
where p.permission_name = 'VIEW ANY DATABASE' and p.state_desc = 'DENY'

And then, as you suggested, perform REVOKE for the role/login returned in the result set. But if there is the permission is not granted GRANT will be also necessary here.

Commented:
Yeah, something along those lines.

There's always a reason in the permissions tables why someone can't get to something. It's normally just a matter of looking for it.

Rob

Commented:
@rob_farley: Personally I don't see any reason for denying VIEW ANY DATABASE permission (especially when we take into account that this causes that the databases are not listed in the Object Explorer).

Commented:
People often do it in environments where they want the list of databases to be secret. Like co-hosted ones.

Commented:
@rob_farley: However, it's interesting that when a user is denied to view any database he can see (in Object Explorer and in sys.databases view) only master, tempdb and the databases in which he is a db_owner.
Commented:
I thought they would have access to see any database to which they are mapped to a specific user (not just db_owner).
Commented:
@rob_farley: That's why I could not imagine the need of taking away the VIEW ANY DATABASE permission from public ;-) I've been testing it for a while and I can confirm - being a user does not guarantee you'll see the database in sys.databases (but you will be able to perform USE DatabaseName...).

Commented:
hmm... anyway - I'm going offline now. It's getting late here.
Leon O'DanielVice President

Author

Commented:
Thank you everyoine for all of the responses. I am hosted with an ISP that it appears to put us in a role that does not have View All Databases permissions. And, we are not given an sa account. We create logins using a web interface, and I can add users to the database using a "command" screen hosted with the ISP, or using SSMS.

I did see the currently logged-in user in the query results when I ran the following query:
select pr.name
from sys.server_permissions p
inner join sys.server_principals pr on pr.principal_id = p.grantee_principal_id
where p.permission_name = 'VIEW ANY DATABASE' and p.state_desc = 'DENY'

I was able to execute the following SQL statement in their "command" interface, but it appears that it had no effect on being able to view this or any other databases on the server:
USE master;
GRANT VIEW ANY DATABASE TO YourLogin

Commented:
@leonod: The permission is probably denied to the public role.
Leon O'DanielVice President

Author

Commented:
I'm going to contact my web host and see what's up. I'll share their answer with everyone.
Vice President
Commented:
Here's the response from my ISP (Intermedia.net):
Please note: According to Microsoft Security Concerns for shared SQL Server installations, "View Any Database"option has been disabled, so that database can be viewed in SQL Management Studio only by database owner. This means that when you are connected to the MSSQL database using additional logins (not using the database owner login), you are unable to view your database in the list of available databases. However you'll be able to execute queries against this database using 'use database' command:

Thank you to everyone who tried to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial