Post reply

execute as owner

  • June 9, 2009 at 8:17 am

    #205344

    A developer is receiving this error when trying to ALTER a sp

    Cannot execute as the user 'owner', because it does not exist or you do not have permission.

    The developer is a member of a db role. The role has the following permissions on the schema:

    GRANT ALTER ON SCHEMA::[yo] TO Role_FlexDeveloper

    GRANT CONTROL ON SCHEMA::[yo] TO Role_FlexDeveloper

    GRANT DELETE ON SCHEMA::[yo] TO Role_FlexDeveloper

    GRANT EXECUTE ON SCHEMA::[yo] TO Role_FlexDeveloper

    GRANT INSERT ON SCHEMA::[yo] TO Role_FlexDeveloper

    GRANT REFERENCES ON SCHEMA::[yo] TO Role_FlexDeveloper

    GRANT SELECT ON SCHEMA::[yo] TO Role_FlexDeveloper

    GRANT UPDATE ON SCHEMA::[yo] TO Role_FlexDeveloper

    GRANT VIEW DEFINITION ON SCHEMA::[yo] TO Role_FlexDeveloper

    The sp contains the clause 'with execute as owner'. Any ideas on what's wrong here?

    Thanks!

  • June 9, 2009 at 8:27 am

    #1006726

    What account owns the object? The dev must have Impersonate permissions on that login, per Books Online, in the chapter on "Execute As". That's most likely what's missing.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • June 9, 2009 at 8:40 am

    #1006736

    Thanks for the reply.

    Could you be more specific?

    The sp is in the yo schema. Where do I look to see who is the owner of the sp? I read BOL; however, it's not specific about what to do as far as impersonation. I don't see Impersonation as a permission anywhere.

    Also, why does the role need this... it's not executing the code, it's doing an ALTER.

    Thanks for your time.

  • June 9, 2009 at 8:46 am

    #1006743

    Search for "impersonation" in BOL. There's a whole section about it. It has all the data in there.

    I really can't be more specific. I haven't run into the problem you're having, all I've done is read about it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

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