Users able to login but not able to see databases

Question

Users able to login but not able to see databases

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

sonal_anjaria SSC Eights! Points: 854 More actions · Answer 1

I had actually run a script to tighten security. We have a Microsoft PFE that works with us, and he thought to look at what privileges allows users to view databases.

Apparently if they were to go to and say use <certaindb> and they had access to it, they would have been able to run queries, just the view any database had been disabled.

Sonal.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

There are multiple connect items for the VIEW ANY DATABASE prevents users from viewing databases they have access to as well issue. I'd recommend that you vote on Erland's item so that the votes are agregated together which gives the issue more visibility.

Need VIEW DEFINITION permissions per database

This is a by design issue and is a change in functionality from SQL Server 2000 to 2005. Enterprise Manager in 2000 would actually query every database to determine if the login had access to connect to that database and based on this display the database to the user. On large servers with hundreds of databases this took a long time, so it was changed in 2005 and is the same in 2008. To correct it would be a fundamental change to SQL, since the permissions for a database are stored in the database, and not at the server level.

The user can still connect to and use the databases, but it makes things like Object Explorer useless. Trade offs in everything. In my shop we don't DENY VIEW ANY DATABASE because of this. Its to much of a hassle, and the login can't access the database if they don't have rights to it, so who cares if they see it. If you were a hosting service provider or ISV I can see the pain point but for internal stuff, does it matter that a Login can see the database names if they can't access them?