Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 509
  • Last Modified:

Stop MSSQL Users from Seeing Everyone's Databases

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
weblocked
Asked:
weblocked
  • 2
1 Solution
 
sachinpatil10dCommented:
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
 
weblockedAuthor Commented:
Thanks guys, but what if the user isn't the database owner? Any way for them to see their databases too?
0
 
Scott PletcherSenior DBACommented:
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
 
weblockedAuthor Commented:
Thank you.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now