Post reply

Granting Select On The Sys Schema

  • December 1, 2015 at 3:16 pm

    #302563

    Greetings,

    I've been banging my head against the wall on this situation and my Google Fu is failing me badly.

    After a recently security audit we were mandated to remove from the public role any unnecessary permissions, especially pertaining to the sys views. The audit findings recommended the use of this script - Remove Public and Guest Permissions - and per the recommendation we tested and kept records of what was changed, as well as migrating some of the permissions to specific database users.

    As it turns out, that didn't work out as expected. We were recently asked to provide metadata-only access to a team from corporate so they could perform a gap analysis against our servers and their Teradata Warehouse (...). Among the things they asked for were the ability to select from specific views in the sys schema (tables, indexes, partition, etc.).

    So after creating the Server Login (a Windows Group), and then creating Database Users in each of the databases they needed to gain access to, I ran the following:

    GRANT SELECT ON SCHEMA::[sys] TO [NEW_DATABASE_USER]

    And went on my merry way. Imagine my surprise when I come in this morning and everything they attempt to run returns:

    The SELECT permission was denied on the object 'tables', database 'mssqlsystemresource', schema 'sys'.

    So I started searching, begging with that exact error message. Every single result I found indicated that either I had mistakenly applied DENY to the user (I had not; I even went so far as to add DENY and then remove them, just in case), or that my master db was corrupted and I was screwed (...).

    Microsoft's own documentation on GRANT Schema Permissions (Transact-SQL) states that using the GRANT syntax above will grant SELECT privileges on a given schema.

    But apparently this must not be the case with the sys schema - or am I missing something?

    If I granted the SELECT permission on the individual views to the database user, they still could not select from them (I verified independently).

    I even went so far as to grant SELECT on the sys schema back to the public role - no dice.

    The only way I could get things to work was to individually grant SELECT on each table to the public role - only then were the users able to select from the views in the sys schema.

    TL;DR: Why does GRANT SELECT ON SCHEMA::[sys] TO [DATABASE_USER]; not work?!

  • January 7, 2016 at 9:27 am

    #1850242

    did you ever resolve this? I need to know the answer myself.

    Thank you.

  • January 7, 2016 at 10:09 am

    #1850262

    Patti Johnson (1/7/2016)

    did you ever resolve this? I need to know the answer myself.

    Thank you.

    Hi Patti - no I didn't. I finally gave up and just reversed the changes.

    Best I can figure, you can't grant SELECT on the SYS schema to a user, it has to be a role, despite being able to grant SELECT on any other schema to a user and having it work as expected.

  • January 7, 2016 at 10:24 am

    #1850266

    Okay. Thank you for replying.

  • January 7, 2016 at 10:52 am

    #1850273

    I believe the permission GRANT VIEW DEFINITION TO [MyDomain\LOWELL] is what you are after.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 5 posts - 1 through 4 (of 4 total)

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