Can I give a Stored Procedure permission to SET IDENTITY_INSERT?

Question

I have a stored procedure that I would like to give permission to insert identities while not giving the restricted user calling it ALTER TABLE privileges. Is that possible?

Here's my stored procedure:

CREATE PROCEDURE [dbo].[AddInternalQu] 
(
    @id [int],
    @qu [nvarchar](500),
    @pg [nvarchar](50),
    @isactive [bit],
    @isdoc [bit],
    @allowNA [bit],
    @textBox [bit],
    @redf [bit],
    @qord [int],
    @shqu [nvarchar](50),
    @restrict [bit],
    @scanwizard [bit]
)
AS
BEGIN
    SET IDENTITY_INSERT [questions] ON;

    INSERT INTO [questions] (qu_id, question, shortqu, redflag, page, active, is_doc_qu, 
                             allowNA, textBox, qu_order, scanwizard, restricted) 
    VALUES(@id, @qu, @shqu, @redf, @pg, @isactive, @isdoc, 
           @allowNA, @textBox, @qord, @scanwizard, @restrict);

    SET IDENTITY_INSERT [questions] OFF;
END

GRANT EXEC ON AddInternalQu TO MyStandardRole;

As it stands a user I add to the MyStandardRole role has the rights to execute the stored procedure but then it fails trying to set the identity insert with insufficient privileges.

Answer 1

"WITH EXECUTE AS OWNER" should do the trick.

CREATE PROCEDURE [dbo].[AddInternalQu] 
(
    @id [int],
    @qu [nvarchar](500),
    @pg [nvarchar](50),
    @isactive [bit],
    @isdoc [bit],
    @allowNA [bit],
    @textBox [bit],
    @redf [bit],
    @qord [int],
    @shqu [nvarchar](50),
    @restrict [bit],
    @scanwizard [bit]
)
WITH EXECUTE AS OWNER
AS
BEGIN
    SET IDENTITY_INSERT [questions] ON;

    INSERT INTO [questions] (qu_id, question, shortqu, redflag, page, active, is_doc_qu, 
                             allowNA, textBox, qu_order, scanwizard, restricted) 
    VALUES(@id, @qu, @shqu, @redf, @pg, @isactive, @isdoc, 
           @allowNA, @textBox, @qord, @scanwizard, @restrict);

    SET IDENTITY_INSERT [questions] OFF;
END

GRANT EXEC ON AddInternalQu TO MyStandardRole;