Error 229 Access to objects

  • smthembu

    Hall of Fame

    Points: 3456

    Hello,

    I have an issue with 2 users who have access to all the db's but two of those db's they get an error when accessing objects

    i have doubled check their permission it has no deny db reader or writer all are db_owner, when i grant them sysadmin as a test it works. Any idea

    error

    It's better to fail while trying, rather than fail without trying!!!

  • John Mitchell-245523

    SSC Guru

    Points: 148744

    I don't think you can assign permissions directly on the hidden mssqlsystemresource database.  You'll need to assign a server-level permission to the user - possibly something like VIEW SERVER STATE.

    John

  • anthony.green

    SSC Guru

    Points: 112438

    do they have read access to master?

    we had this recently on a 2012 box and changing user permissions to be ssis_admin, they couldn't run the ssis reports without getting errors with mssqlresource databases, granting read to master was a dirty fix to solve the issue.

  • smthembu

    Hall of Fame

    Points: 3456

    No permission on master but i will give it a try and feedback if all working

    It's better to fail while trying, rather than fail without trying!!!

  • Jeff Moden

    SSC Guru

    Points: 996436

    smthembu wrote:

    Hello,

    I have an issue with 2 users who have access to all the db's but two of those db's they get an error when accessing objects

    i have doubled check their permission it has no deny db reader or writer all are db_owner, when i grant them sysadmin as a test it works. Any idea

    error

    A larger question would be... why do you have low priv'ed users that need to examine query store?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Forum FAQ

  • Sue_H

    SSC Guru

    Points: 90659

    smthembu wrote:

    No permission on master but i will give it a try and feedback if all working

     

    According to the documentation, they need view database state permissions:

    sys.query_store_runtime_stats (Transact-SQL)

    Sue

  • smthembu

    Hall of Fame

    Points: 3456

    They are not viewing query store, this is from a user db which they have db_owner permission but below erro

    error1

    It's better to fail while trying, rather than fail without trying!!!

  • smthembu

    Hall of Fame

    Points: 3456

    Granting access to the master db it didn't work

    It's better to fail while trying, rather than fail without trying!!!

Viewing 8 posts - 1 through 8 (of 8 total)

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