Request from OEM Partner:
We are starting to deploy installations in hosted environments and there was one issue that came up. In these environments the hosting company may have, say 20 SQL databases on a single server with each database belonging to a different client. The issue is that they want to make sure that when the client going into the program to select a database that they can only see the databases that they have access to, otherwise they will see a list of all of the hosting company’s other clients as well. We know that they won’t be able to actually access the databases but the fact that they are visible is a problem for us.
When I go to select a database in the program it shows every database on the server. Is there a way to only show databases that the current user has access to? I’m not sure how it works but it seems like when the database lookup is select it probably passes something like “SELECT name FROM sys.databases” and returns the list? Altering it to be something like the query below would easily resolve this:
select name from sys.databases
where HAS_DBACCESS(name) = 1