|
I'm trying to use a stored proc with execute as owner to enable standard users to do something they can't normally do. But it's giving me error 916 when I try to execute it. Here's the stored proc: The idea is to copy in any Departments from the other database that we don't have in this one. When I execute it I get this error message:
I'm stumped. sa is a member of the sysadmin role and I haven't denied it anything. sa owns both databases and is in the dbowner role in both databases. Both tables belong to the dbo schema. I'm logged in as myself and I'm a member of the sysadmin role. I've tried a bit of diagnostics. I added some selects to see what 'owner' means at runtime and here are the results: suser_name() and suser_sname() return 'sa' while user_name() returns 'dbo'. That seems OK. I tried changing it to "with execute as 'sa'" but that gives me this error when I try to run the CREATE PROC:
This looks wrong. I'm a sysadmin. The only things I'm finding about error 916 are strange things about SSMS bugs and unticking the Collation column in Object Explorer Details. I even tried that but it didn't change anything. What am I not understanding? Please help!
(comments are locked)
|
|
Hi David, Are you creating the Membership_DepartmentSync proc in a database other than golfer? I ask as you are referencing tblDepartment as explicitly being in the golfer database. from http://www.sommarskog.se/grantperm.html#EXECUTE_AS
I think this may be at least part of your issue. There is a fuller explanation by Dan Guzman of a similar issue at http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/319601a0-196a-4422-ad34-044ff7ad4363 and in BOL at http://msdn.microsoft.com/en-us/library/ms188304(SQL.105).aspx. The latter article also explains how you can change the scope if you need to do this. Hope that helps, Dave. And another veil is lifted! Thanks, Dave.
Sep 11 '12 at 09:17 AM
David Wimbush
(comments are locked)
|
|
Has the current database, or the 'golfer' database been restored? Are there any insert triggers firing on dbo.Membership_Department? Thanks, Kev. The answers are no and no but I think Dave's put me on the right track.
Sep 11 '12 at 09:15 AM
David Wimbush
(comments are locked)
|

