Solved

Stop MSSQL Users from Seeing Everyone's Databases

Posted on 2012-04-05
4
505 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article explains how to install and use the NTBackup utility that comes with Windows Server.
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

623 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