SQLTeam.com | Weblogs | Forums

Visibility of other databases despite restricted access


#1

Hi all,

We give a few select clients direct ODBC access to their database. The problem is that they are on a DB Server that hosts other client databases. Even though they only have access to their database, there doesn't seem to be a good way to not show the other databases even though they can't access them. Does anyone have a good way to handle this?

Thanks.


#2

not with SQL. The engine simply hides that information based on your permissions. An external document perhaps?

Or, you could grant access to the database but deny access to everything in it. A little bit more cumbersome, but may give you what you want


#3

Have you looked at the VIEW ANY DATABASE permission. My vague memory is telling me that may be what you want.


#4

SQL server 2014 and up. Also:

By default, the VIEW ANY DATABASE permission is granted to the public role. Therefore, by default, every user that connects to an instance of SQL Server can see all databases in the instance.

So if the OP was using 2014, anyone who logs in should already be able to see all databases


#5

I believe the OP does NOT want the users to see all other databases. If that is the case - as I suspect - then removing the permission VIEW ANY DATABASE from the public role should resolve the issue. I am not sure if there are any ramifications of doing so - I would recommend testing it before implementing in a production environment.


#6

This issue is a very serious pain to try to work around! I finally decided it wasn't worth the trouble.