System Table Access

  • In the wake of the recent SQL Injection attacks, I've been locking down security on our system tables. The good news is that they're no longer visible to our application logins. I created a role that has been denied select on the system schema and that seems to work well.

    There are instances in which I need a SQL Server Login to be able to see system tables. To solve that I have a System Table Reader user and will utilize this in an "EXECUTE AS" scenario whenever needed.

    Problem is, I can't get this new user to see the system tables. Here's what I do:

    1) Create SQL Server Login AppUserSystemTableReader

    2) Place in db_datareader database role for the database (no others)

    3) Query system tables fails in this database. Query system tables in Master does work.

    4) Execute "GRANT SELECT ON SCHEMA::sys TO AppUserReadSystemTables" in database. No luck.

    Am I missing something? Placing the user into the dbo still denies select on the system tables. Placing the user into the sa role is the only way I can get it to query the system tables.

    Any thoughts/recommendations would be appreciated. Thanks!

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply