4

By default if you connect to a remote SQL Server via an account that has access to say 1 of the 10 databases. You will still see in the Object Explorer all other databases, obviously due to permissions you cannot actually query them, but you can see their names.

I have heard that there is a method that disable this behavior, but I've been unable to find the answer, does anyone know how to do this? To give an example I have a SQL Server called MyDbServer, it has 4 databases,

  1. MyDatabase
  2. YourDatabse
  3. PrivateDatabase
  4. ReallyPrivateDb

If you connect via an account that only has permissions to "YourDatabse" you will still see a listing of all other databases, attempts to query will grant "select" permission denied or a similar error.

For security resons, we DO NOT want users to see any database other than the ones they are mapped to.

Dylan Corriveau
  • 2,561
  • 4
  • 29
  • 36
Mitchel Sellers
  • 62,228
  • 14
  • 110
  • 173

3 Answers3

1

This blog talks about methods for hiding DBs for both SQL 2000 and SQL 2005.

katzbatz
  • 332
  • 2
  • 16
Tom H
  • 46,766
  • 14
  • 87
  • 128
1

After having my client struggle with the identified resources I did some testing and created this blog posting with a bit more context and instruction on how to get this working.

katzbatz
  • 332
  • 2
  • 16
Mitchel Sellers
  • 62,228
  • 14
  • 110
  • 173
  • It appears that the downside is that only one user at a time can be the dbo. Therefore, the client can only have 1 database user... Is that right? – NotMe Oct 15 '08 at 21:50
0

The short of it is:

use master
go
deny VIEW any DATABASE to login1
go

where login1 is the login account that you want to limit.

NotMe
  • 87,343
  • 27
  • 171
  • 245
  • 1
    Actually this doesn't get it all the way, the blog article, was a bit hard to follow, but the key is that the user must be DBO of the article in question – Mitchel Sellers Oct 15 '08 at 21:17
  • It would be nice if in SQL Server Management Studio if you could right click on a database and say show only this database for this profile. But it would never be that easy. My scenario is when I log onto my SQL Server at Godaddy using SQL Server Management Studio I see hundreds of other databases and have to scroll down everytime. Next time I will name my database "_AAAMydatabase" – Eric Bishard Oct 15 '14 at 23:53