Solved

Stop MSSQL Users from Seeing Everyone's Databases

Posted on 2012-04-05
4
499 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

Why are Office 365 signatures so complicated?

Trying to setup transport rules for Office 365 email signatures and can’t quite figure it out? Having to test the signature over and over? Make things simple by using Exclaimer Cloud - Signatures for Office 365.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
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…

911 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

18 Experts available now in Live!

Get 1:1 Help Now