Solved

Stop MSSQL Users from Seeing Everyone's Databases

Posted on 2012-04-05
4
498 Views
Last Modified: 2013-09-09
We're running an MSSQL 2008 Web R2 server with hundreds of databases and hundreds of database users.

Problem(current outcome): When a user connects via MSSQL Management Studio, he/she can see the full list of databases but can only access the tables of the database his/her user has access to.

Desired Outcome: When a user connects via Management Studio using their designated MSSQL username/pass, they only see a list of databases the MSSQL username is assigned to. They should not see the whole list of databases.
0
Comment
Question by:weblocked
  • 2
4 Comments
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 37815116
Use following command

To give grant
 
GRANT VIEW ANY DATABASE TO <login>; 

Open in new window


To revoke grant

REVOKE VIEW ANY DATABASE TO <login>; 

Open in new window

0
 

Author Comment

by:weblocked
ID: 37815121
Thanks guys, but what if the user isn't the database owner? Any way for them to see their databases too?
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 37816274
That's tricky.  I've had to try to do that.


You could try this:

REVOKE VIEW ANY DATABASE
    FROM <login>;

USE <db_name>

GRANT VIEW DATABASE STATE
    TO <username>;
0
 

Author Closing Comment

by:weblocked
ID: 38051233
Thank you.
0

Featured Post

Do email signature updates give you a headache?

Do you feel like you are constantly making changes to email signatures? Are the images not formatting how you want them to? Want high-quality HTML signatures on all devices, including on mobiles and Macs? Then, let Exclaimer solve all your email signature problems today.

Join & Write a Comment

Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
A procedure for exporting installed hotfix details of remote computers using powershell
This tutorial will walk an individual through locating and launching the BEUtility application and how to execute it on the appropriate database. Log onto the server running the Backup Exec database. In a larger environment, this would generally be …
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now