Post reply

View Definition Permission on sys Schema

  • October 12, 2017 at 5:36 pm

    #325803

    Hi All,
    I have a third party database and application that is using high level permissions to connect to the DB. I'm in the process of reducing the permissions of the application user to just those required for the application to function. I'll illustrate the issue I'm running into by way of example. I've simplified things to illustrate the issue more clearly.

    USE myDB
    CREATE USER Peter1 WITHOUT LOGIN
    GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::[dbo] TO Peter1;

    Using the above permissions, the application is failing when it's calls a given proc. Within the proc is this select statement:


    SELECT value
    FROM sys.fn_listextendedproperty(default, default, default, default, default, default, default)
    WHERE name = 'XXX'


    With the above permissions this will return an empty set (running as sysadmin returns the proper data). Even running the individual select statement outside of the proc under the user's context results in an empty set. I found that I must grant VIEW DEFINITION permission on the entire database for the user to be able to successfully execute the query against fn_listextendedproperty. If I try to grant the VIEW DEFINITION on only the sys schema it doesn't work?

    I curious as to why granting the view definition permission against only the sys schema doesn't work, but granting the permission on the entire database does.
    Is there anything inherently "wrong" with granting view definition on the entire database versus only on the sys schema.

    Any thoughts would be most appreciated.
    Thanks,
    Peter

  • October 12, 2017 at 5:59 pm

    #1963487

    Extended properties are part of the definition of the objects they are associated with.

  • October 13, 2017 at 11:23 am

    #1963623

    You are querying the extended properties for the database itself which isn't in the sys schema.
    Permissions needed are based on the objects for which you are trying to get extended properties.

    If you did something like this in AdventureWorks:
    GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::[Person] TO WhateverLogin;
    Then WhateverLogin would be able to view the properties for objects in the Person schema. So this would work:
    SELECT value
    FROM fn_listextendedproperty (NULL, 'schema', 'Person', 'table', 'Address', 'column', default);
    However, trying to get the extended properties for the database itself would be an empty result set:
    SELECT value
    FROM sys.fn_listextendedproperty(default, default, default, default, default, default, default)
    And querying the extended properties for objects in a different schema would result in no rows as well:
    SELECT value
    FROM fn_listextendedproperty (NULL, 'schema', 'Sales', 'table', 'Store', 'column', default);

    Sue

  • October 13, 2017 at 11:51 am

    #1963626

    Very helpful. Thanks Sue!

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

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