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:
The server principal "sa" is not able to access the database "golfer" under the current security context. 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:
Cannot execute as the user 'sa', because it does not exist or you do not have permission. 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!
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.
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,
answered Sep 11, 2012 at 08:03 AM
Has the current database, or the 'golfer' database been restored?
Are there any insert triggers firing on dbo.Membership_Department?
answered Sep 11, 2012 at 08:05 AM
Kev Riley ♦♦