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

  • 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

    Thursday, March 25, 2010 8:16 PM

Answers

  • 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
    • Marked as answer by Mike DeFehr Thursday, March 25, 2010 8:52 PM
    Thursday, March 25, 2010 8:24 PM

All replies

  • 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
    • Marked as answer by Mike DeFehr Thursday, March 25, 2010 8:52 PM
    Thursday, March 25, 2010 8:24 PM
  • 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
    Thursday, March 25, 2010 8:56 PM