none
permissions to query sys.sql_expression_dependencies

    Question

  • Hi,

    we have a process which queries the sql_expression_dependencies for each database on a server.

    This works for most databases but I am getting the following error message for some of the databases

    Msg 229, Level 14, State 5, Line 2
    The SELECT permission was denied on the object 'sql_expression_dependencies', database 'mssqlsystemresource', schema 'sys'.

    This is when the query is prefixed with the database name i.e.

      SELECT referencing_id, OBJECT_SCHEMA_NAME(referencing_id),
                            OBJECT_NAME(referencing_id), referenced_server_name,
                            referenced_database_name, referenced_schema_name,
                            referenced_entity_name FROM <Database Name>.sys.sql_expression_dependencies
                            ORDER BY referencing_id, referenced_id

    However if I connect to the user database in question and run the query without the database name

    use <database name>
     SELECT referencing_id, OBJECT_SCHEMA_NAME(referencing_id),
                            OBJECT_NAME(referencing_id), referenced_server_name,
                            referenced_database_name, referenced_schema_name,
                            referenced_entity_name FROM sys.sql_expression_dependencies
                            ORDER BY referencing_id, referenced_id

    It works.

    This is only occurring with a number of databases and I can't see any difference in the permissions I've granted.

    I'd be grateful for any suggestions as to why this might be happening or what else I can do to investuiigate this further.

    Thanks


    Sean

    Tuesday, October 29, 2013 6:40 PM

Answers

  • Do you have following permissions on the database where it is failing..

    VIEW DEFINITION permission on the database and SELECT permission on sys.sql_expression_dependencies for the database.


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    • Marked as answer by Sean2000 Wednesday, October 30, 2013 2:14 PM
    Tuesday, October 29, 2013 7:16 PM

All replies