Home Forums SQL Server 2008 Security (SS2K8) How can i apply Deny Permission regarding sys.database and sys.tables etc to users ? RE: How can i apply Deny Permission regarding sys.database and sys.tables etc to users ?

  • By default all users are a member of the Fixed Server Role public, and this cannot be changed. Also by default, all members public can see all databases on the instance, i.e. they can see that they exist, but cannot access them. UNlike the way sys.tables works where if you do not have any permission to work with the table you cannot see it in the sys.tables view, even if a login has no permissions to work in a database they can still see the database in sys.databases.

    You can DENY the VIEW ANY DATABASE permission from the login if you want to prevent them from seeing any databases, but they will also not see databases they actually have access too, so be sure that is what you want.

    DENY VIEW ANY DATABASE TO [login_name_here];

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato