SQL error running WITH EXECUTE AS

I tried to add the following to my stored procedure but it did not work

WITH EXECUTE AS 'sa'

I got an error reading.

Cannot execute as the user 'sa', because it does not exist or you do not have permission.
Mr_ShawAsked:
Who is Participating?
 
AmmarRCommented:
Execute as will impersonate the usage of the store procedure

to know more about it read this great article

http://www.sommarskog.se/grantperm.html#EXECUTE_AS
0
 
EvilPostItCommented:
SA is a login not a user.

Have you tried execute as 'dbo'?
0
 
Mr_ShawAuthor Commented:
I have a stored procedure which executes an Agent Job.

The Agent Job is owned by the SA.

should i not write WITH EXECUTE AS 'sa' ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
subhashpuniaCommented:
You can use "Login_Name" in Execute AS clause only for DDL triggers with server scope or logon triggers.

For more information please check the article:
http://msdn.microsoft.com/en-us/library/ms188354.aspx
0
 
Mr_ShawAuthor Commented:
how about

WITH EXECUTE AS LOGIN = 'sa'
0
 
subhashpuniaCommented:
Inside stored procedure use as: WITH EXECUTE AS 'dbo'



0
 
EvilPostItCommented:
subhashpunia,

Please see the second comment.......
0
 
Mr_ShawAuthor Commented:
using dbo through back a permission error.

I'll read through all the material and try again.

Thanks
0
 
AmmarRCommented:
Dear Mr_Shaw:

EvilPostIt: post to use dbo instead of sa will solve your initial problem but soon after that you will get the below error

The server principal "User" is not able to access the database "DB" under the current security context.

so i suggest reading the article i posted above and knowing every thing about Execute as before using it, its not  a straight forward thing and has many considerations.

Regards
0
 
EvilPostItCommented:
Hi AmmarR.

Agreed, you should always know what your using. Plus with my original posting i didnt realise this was executing something outside of the current database.
0
 
AmmarRCommented:
Sure EvilPostIt:
Agree

good day
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.