Visible Databases

Question

Post reply

Visible Databases

Vasu-709835

SSC Eights!

Points: 921
More actions
May 14, 2010 at 2:36 am

#386930

Hi Friends,

I am having 3 database called

1>Finance1

2>Finance2

3>Finance3

Having a user called Support,

I am given DBreader permissions on Finance1 Database,

But when this user logins ,

he can able to see all databases in the server.Although He able to access Only one

1>Finance1 Database only

My Question is

Only Finance1 should be visible when he logins , How do this?

Thanks for u r help

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

you have end users logging into your Server using SSMS?

anyway, I'm thinking because CONNECT rights give them read access to the master database, and sys.databases has the list of databases, they can see them; i'm researching it now, this is kind of interesting.

I have a couple of accounts on some shared hosting websites, and there i can also see the hundreds of databases on the shared SQL Server, but i can access only one of them; not sure if it's a real issue, that he can find out the name of a database.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323482 More actions · Answer 2

from msdn:

http://msdn.microsoft.com/en-us/library/ms189077.aspx

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. To verify this behavior, run the following query:

so if you DENY VIEW ANY DATABASE TO <yourlogin>, they cannot see any database in SSMS Object Explorer except master and tempdb...even if they've been granted access to, say, the SandBox database, even if he's made the database owner.

still trying to get my sample user to see any databases he has access to, not working yet...............

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Arthur Kirchner Ten Centuries Points: 1046 More actions · Answer 3

Lowell (5/14/2010)

you have end users logging into your Server using SSMS?

Just a little real world confirmation: our customer's DB ist hosted by a BIG provider and we have remote access

for Debugging via SSMS. On the same instance we can see the DB of other HUGE companies.

So: - despite that I wonder why the instance is shared - it doesn't seem to be trivial to make the DB invisible...