How to grant a Login Read Only access to a database and deny viewing other database names.

Question

I understand that I can create a login and grant that login read-only access to one database and restrict access to other databases - however, I also want to remove the ability for that login to even see that other databases exist.

I can almost accomplish this by making my login the database owner, then denying the "VIEW ANY DATABASE" permission as outlined in Mitchell Sellers article here: http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/260/limit-sql-server-database-list.aspx

- however - this requires that I make my login the database owner which gives it more rights than I want to give it.  I am looking for a way to grant a Login read-only rights to *one* database and have that database the *only* thing it sees

Answer 1

Both cannot be achieved at the same time and this is how sql security heirarchy is designed. We have had a lot of discussion in the same forums for this same case - http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/64aa6538-5f57-491b-9c53-4a103ae71637

Though you have got a right solution of granting database owner , yes this will have excessive privilege with db_owner and only read-only is not possible.
But one thing you can possibly do here is run a revoke view any database to login
and this will not even allow the user to see his database where he has read only access ( but he will be able to execute the select statements in queries and applications), he will only see tempdb and master.

---

Thanks, Leks

Answer 2

Of course! That will work fine - the login doesn't need to "see" that database - I suppose the only disadvantage there is that it will not be able to browse around the database in object explorer... we can probably live with that