Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Stop MSSQL Users from Seeing Everyone's Databases

Posted on 2012-04-05
4
Medium Priority
?
508 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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

OfficeMate Freezes on login or does not load after login credentials are input.
A safe way to clean winsxs folder from your windows server 2008 R2 editions
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
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…

916 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