System Table Access

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Security » System Table Access

System Table Access

Rate Topic

Display Mode

Topic Options

Author

Message

nuberfin

Posted Thursday, September 18, 2008 9:32 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, June 07, 2013 3:31 PM
Points: 7, Visits: 280

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 »

Permissions