Sql-server – SQL Server 2008 R2 – Limit viewing on database list

sql serversql-server-2008-r2

In SQL Server 2008 R2 I have user1, he owns 3 db (db1 , db2, db3) and I want he can see only these 3 db when he login by using SSMS.

I did REVOKE VIEW ANY DATABASE FROM PUBLIC, but then he cannot see any db in list.
How can I fix it?

Best Answer

  1. Make sure his server login exists.(Meaning you dont delete this!!!)
  2. DENY VIEW ANY DATABASE TO user (I think this is where you are stuck right now as the user is unable to view any databases.)
  3. Then Delete his user logins from those 3 databases that he is a database owner on.
  4. Then go to each database > right click > properties > files > Choose his login as the database owner on those 3.

This way he will remain the database owner on those db's and also not be able to view the other databases on that instance.

Tried and TESTED!!

Related Question