Stored proc with execute as owner: error 916

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:

 CREATE PROC dbo.Membership_DepartmentSync
     WITH EXECUTE AS owner
 SET nocount ON;

 SET IDENTITY_INSERT dbo.Membership_Department ON;

 INSERT  dbo.Membership_Department
         SELECT  DepartmentID,
         FROM    golfer.dbo.tblDepartment
         WHERE   IsDeleted = 0
                 AND DepartmentID NOT IN (SELECT DepartmentID
                                          FROM   dbo.Membership_Department);

 SET IDENTITY_INSERT dbo.Membership_Department OFF;

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:

Msg 916, Level 14, State 1, Procedure Membership_DepartmentSync, Line 10

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:

Msg 15151, Level 16, State 1, Procedure Membership_DepartmentSync, Line 2

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!
more ▼

asked Sep 11, 2012 at 07:42 AM in Default

avatar image

David Wimbush
10.7k 30 34 43

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

"When you impersonate a database user you are by default sandboxed into that 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,


more ▼

answered Sep 11, 2012 at 08:03 AM

avatar image

Dave_Green ♦
5.5k 4 5 10

And another veil is lifted! Thanks, Dave.

Sep 11, 2012 at 09:17 AM David Wimbush
(comments are locked)
10|1200 characters needed characters left

Has the current database, or the 'golfer' database been restored?

Are there any insert triggers firing on dbo.Membership_Department?

more ▼

answered Sep 11, 2012 at 08:05 AM

avatar image

Kev Riley ♦♦
64.9k 48 62 81

Thanks, Kev. The answers are no and no but I think Dave's put me on the right track.

Sep 11, 2012 at 09:15 AM David Wimbush
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 11, 2012 at 07:42 AM

Seen: 5150 times

Last Updated: Sep 11, 2012 at 09:17 AM

Copyright 2016 Redgate Software. Privacy Policy