x

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
AS 
SET nocount ON;

SET IDENTITY_INSERT dbo.Membership_Department ON;

INSERT  dbo.Membership_Department
        (DepartmentId,
         Name)
        SELECT  DepartmentID,
                [Description]
        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

David Wimbush gravatar image

David Wimbush
5.1k 29 31 34

(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,

Dave.

more ▼

answered Sep 11, 2012 at 08:03 AM

Dave_Green gravatar image

Dave_Green ♦
4.2k 3 4 8

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

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x168
x163

asked: Sep 11, 2012 at 07:42 AM

Seen: 3489 times

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