Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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