[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

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.
0
Mr_Shaw
Asked:
Mr_Shaw
  • 3
  • 3
  • 3
  • +1
4 Solutions
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now