Hello people,

 

Is there a way to Restricting what databases a user can see in SQL Management Studio.....

 

What am doing is in SSMS, i create a new user called "Mike" and assign him db_owner, sysadmin and security admin rights. Then he logs into the SSMS with his credentials and creates few databases for his own need

 

Now, i create a new user again called "Sam" and assign him with the same permission as "Mike". He also creates few databases of his need in the same SQL instances where "Mike" has created databases

 

Now, i want to restrict "Mike" to see the databases created by "SAM" and vice versa. So, is there a way where ican provision my SQL environment so that databases are not seen in the Instance tree when they connect to the same instance and they see only their databases?

• Edited by Pallav S. Srivastav Thursday, January 26, 2012 4:05 PM

Coolbudy3002

Try following,

Select Security -> Logins -> Mike , right click and select properties , Click on UserMapping, and select the DBs which you want to be accessed by Mike, and then click Ok

Hello,

First let me point out the following:

1. sysadmin is a SERVER level role and they have complete control over the instance. If they have this, you can't restrict them.

2. db_owner is a DATABASE level role which maps the login to the dbo user, they have complete control over the database. The OWNER of the database is dfferent than being in the dbo role.

3. Security Admin is a SERVER level role, which they would be able to add users to databases that maybe they shouldn't have. Adding this is redundant as the logins already belonged to the sysadmin role.

In your scenario above you wouldn't be able to restrict them as they belonged to the SA role. As far as I know in 2005/2008/R2 the only way to restrict users from seeing databases in SSMS is to revoke VIEW ANY DATABASE to them and public. If you do this only DATABASE OWNERS will be able to "see" their database in the tree view in SSMS. Others still have ACCESS to the database, they just won't see it in their list.

There may be another way but as far as I know this is the only one.

-Sean

• Proposed as answer by Maggie LuoMicrosoft community contributor, Moderator Friday, January 27, 2012 7:58 AM
• Unproposed as answer by Pallav S. Srivastav Monday, January 30, 2012 5:23 PM
• Marked as answer by Pallav S. Srivastav Wednesday, February 15, 2012 12:23 AM

How can i revoke VIEW ANY DATABASE option in SQL Server Management studion 2008 R2?

I Used "DENY VIEW ALL DATABASE TO PUBLIC" and the user in other domain was not able to see the databases and also the database of whose he is the owner.

DENY VIEW ALL DATABASE TO "domain\SAM" is not working on the master db

After hours of trying to figure out how to create a user account which only has access to 1 DB, and can only see that DB. I think i figured it out!!!!

1. Create a user account (make sure its not mapped to any Database)
2. Right Click on the upper section of the SQL (SQLSERVER Name)>Properties>Permissions>Click on the user account, and select Deny to view databases.
3. Right Click on the newly created DB, Properties,Files, and change the Owner to the newly created account.

At this point, once the user logs in he will see the Master,tempdb and will also see the new DB which he is a DB Owner of.

 

Pallav Srivastav

• Marked as answer by Pallav S. Srivastav Monday, January 30, 2012 5:51 PM

This is exactly what I posted previously, did I not make myself clear enough? What did you seem to have issues with understanding the post?

-Sean

I was able to understand what you meant, but as i am nor a SQL engineer i was not able to make it working. I posted few hurs before the last answered post about how to do it after struggling for few hours.

Only that was the issue am not saying that your answer was incorrect. I apologize if it made you fell so.

Thanks Sean for atleast guiding me to te Right Path!!!!! :-)

Hi Pallav,

Thanks a lot for these 3 simple steps, these worked like a miracle.......

Many Thanks

sandeep

System/Network Admin

Unable to see SQL Server -> Properties -> Permissions section as you mentioned in step 2 in SQL Management Studio.

DENY VIEW any DATABASE TO PUBLIC;

GRANT CREATE DATABASE TO PUBLIC;

ALTER AUTHORIZATION ON DATABASE::testdb TO testuser; 

Here is my test result:

• Edited by Leon Zeng 22 hours 23 minutes ago