execute as owner

Question

execute as owner

rocky

SSCrazy

Points: 2120
More actions
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!

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

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

rocky SSCrazy Points: 2120 More actions · Answer 2

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.

GSquared SSC Guru Points: 260824 More actions · Answer 3

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