Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

System Table Access Expand / Collapse
Posted Thursday, September 18, 2008 9:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 28, 2016 5:07 PM
Points: 7, Visits: 377
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!
Post #571890
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse