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?

  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!!

  • Thank you very much, It works for me. I want to ask one more things: user2 have login (db_owner) on db1. How can I make user2 can only see db1 when he logins by using SSMS ? user1 still is chosen in "right click db1 > properties > files" as database owner. – Jack Chuong Apr 8 '14 at 4:40
  • Sounds good.Please mark it as answer if it resolved the issue.You cannot have multiple db_owners to one database! – user2815760 Apr 8 '14 at 4:54
  • You will not be able to hide the databases without having to make the user a db_owner! More information can be found here explaining this scenario: stackoverflow.com/questions/935018/… – user2815760 Apr 8 '14 at 5:03
  • Thanks, I think I can have multiple db_owner to one database by click database > Security > Users > right click a Login > Properties > add role membership as db_onwer ? I read topic stackoverflow.com/questions/935018/… , they conclude that it's complicated because "the permissions are stored at the database level, so it would require enumerating each database to determine if the user has connect rights to display the database in the object explorer, which is an expensive task to perform and how the older EM used to do things". – Jack Chuong Apr 8 '14 at 6:58
  • By the way your solution is enough for me now, I chose your answer as active but cannot vote it because I don't have enough reputation. – Jack Chuong Apr 8 '14 at 7:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.