Solved

Stop MSSQL Users from Seeing Everyone's Databases

Posted on 2012-04-05
4
502 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:
Scott Pletcher 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…

839 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