Hiding databases based on login

Question

Hiding databases based on login

Bob JH Cullen

SSCrazy

Points: 2082
More actions
August 7, 2007 at 7:35 am

#177974

Hi - Does anybody know if it is possible to prevent a database from being displayed in object explorer if the user has no rights to it? We have a multi-client system and want to arrange that when SQL Management Studio is opened by a person who only has rights to, say, DB1, that none of the other databases on the server show up. At present, we can stop the user accessing the other DBs, but he still knows they are there.
Thanks in anticipation
Bob

Viewing 6 posts - 1 through 5 (of 5 total)

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

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 1

Check and make sure that your user does not have any server level permissions - if the user only has permissions to specific databases default behavior in 2005 is to show the user only the databases they have permissions to access.

Joe

t perez SSC Rookie Points: 46 More actions · Answer 2

I have not observed this scenario. I have a user "bireader" on a database "BI_DW" that only has SELECT permissions on the tables in that database. This user can still see all the databases listed in Object Explorer of SSMS.

The user account is for a login of the same name ("bireader") on the SQL Server. As far as I know this is required -- all logins are at the server level, which are mapped to users at the database level.

Beyond this the account has no "server" permissions.

Bob JH Cullen SSCrazy Points: 2082 More actions · Answer 3

Hey! I'm glad it's not just me going nuts

This is exactly what I mean, and I can't find a way to suppress the ones he has no right to use. It's not really the end of the world, but it would be nicer if I could make it work in what seems to be the "logical" way.

Bob

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 4

You are both right, I stand corrected, as part of our regular hardening of servers we revoke VIEW ANY DATABASE as a matter of course.

Take a look at VIEW ANY DATABASE in BOL, the upside is that it can be revoked globally or for individual users/groups - be warned that the only database the user can then see are system databases and those databases which they own - db_owner is not enough the user/group has to be the database owner.

Joe

Bob JH Cullen SSCrazy Points: 2082 More actions · Answer 5

Joe -

Thanks. That looks like it will take us a long way. But in a multi-client environment, it would be good if the restricted users could not see the system db's either - just the ones they are paying to use. Anyway - I'll have a play with that.

Bob