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!