locked
View Definition Permission on sys Schema RRS feed

  • Question

  • 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

    Thursday, October 12, 2017 11:43 PM

All replies

  • Hi AZ_Pete,

    Your query should list the extended properties value for the database object, but I cannot repro the same issue on SQL Server 2014/2016/2017. The only time it returns an empty result is when you explicitly denied VIEW DEFINITION to the user. Here’s how I test it:
    USE AdventureWorks2014;
    CREATE USER TEST2 WITHOUT LOGIN;
    EXECUTE AS USER = 'TEST2'; ----Create and execute as TEST2
    GO
    
    SELECT
    	USER_NAME( ),
    	value
    	FROM sys.fn_listextendedproperty( DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT ); --That returns value.
    GO
    
    REVERT; --Go back to origional user.
    GO
    
    DENY VIEW DEFINITION TO [TEST2];--
    EXECUTE AS USER = 'TEST2'; ----
    GO
    
    SELECT
    	USER_NAME( ),
    	value
    	FROM sys.fn_listextendedproperty( DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT ); --That returns empty set.
    GO
    
    
    REVERT; --Cleanup
    GO
    
    DROP USER [TEST2];
    GO
    


    At this moment, my only assumption is that some changes were made to public database role , could you please verify that?

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 13, 2017 5:57 AM
  • The user needs VIEW DEFINITION on the object you asking sys.fn_listextendedproprty to return data for. If that is the database itsef, only SELECT permission on the dbo schema is not enough, but it must be on database level.

    The fact that the function is in the sys schema has noting to do with it. What matters is the object your requiring data for.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, October 13, 2017 8:23 PM