Limit Database List to Ones With Permission SQL Se

问题:

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.

回答1:

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

回答2:

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.

回答3:

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.