SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


System Table Access


System Table Access

Author
Message
nuberfin
nuberfin
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search