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.
REVOKE VIEW ANY DATABASE FROM PUBLIC, but then he cannot see any db in list.
How can I fix it?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.)
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!!