EXECUTE AS WITH COOKIE from stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

EXECUTE AS WITH COOKIE from stored procedure

Author

Topic

Pifagor
Starting Member

1 Post

Posted - 2011-06-07 : 02:49:01

Hi everybody!

I have such stored procedure:
CREATE PROCEDURE dbName.MyProcedure
AS
DECLARE @cookie varbinary(100)
EXECUTE AS User='dbo' WITH COOKIE INTO @cookie
GO

and try to execute it:
DECLARE @return_value int

EXEC @return_value = dbName.[MyProcedure]
GO

I have this error message:
Msg 15590, Level 16, State 1, Procedure MyProcedure, Line 5
Can only use the 'No Revert' or 'Cookie' options with the 'Execute As' statement at the adhoc level.

If alert the procedure like this:
ALERT PROCEDURE dbName.MyProcedure
AS
DECLARE @cookie varbinary(100)
EXECUTE AS User='dbo' --WITH COOKIE INTO @cookie
GO

it works.

Could anybody explain me why I can't use EXECUTE AS with "WITH COOKIE INTO @cookie"?

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-07 : 03:28:14

As the error says; you can only so this ad-hoc (ie as a standalone query in q query window).
Not part of a stored procedure.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources