Restrict viewing of databases to users in SSMS

Question

Restrict viewing of databases to users in SSMS

sdmgill

SSC Veteran

Points: 249
More actions
December 5, 2008 at 4:36 pm

#383527

I have an interesting problem. We are setting up a new server and we only want people with access to a particular database to see that database in SSMS. That person should not be able to see any databases to which he or she does not have access. By default, all the databases on the server are listed and the user receives an error when they try to open a database they don't have access to (as expected). I read a Technet article from MS, but the solution does not work. A co-worker of mine mentioned that he saw a post in one of the Microsoft forums claiming that a bug is SSMS prevents this type of restrictive viewing but I was wondering if anyone in here found a clever work around. Thanks in advance for your help.

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Can you post the link to the TechNet article you read that shows a way to do this? I don't know how that would be possible because all logins by default have some access to the system views and need that access.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

sdmgill SSC Veteran Points: 249 More actions · Answer 2

Here it is

http://www.microsoft.com/technet/serviceproviders/wbh4_5/CMSU_DH_Plan_CONC_SQL_Server_Management_Studio.mspx?mfr=true

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Thanks for posting the link.

I played around a little bit, but could not find a way other than making the user the owner of the database and that will only work for one user.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Anam Verma SSCertifiable Points: 5365 More actions · Answer 4

i am not sure how this is achieved but i remember that one of my friend had hosted a website and web hosting company has enabled these options restrict view options and he was able to view his database only.

it would be interesting to know the solution of this problem.