asked on

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.

ASKER CERTIFIED SOLUTION

AmmarR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

EvilPostIt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mr_Shaw

ASKER

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' ?

SOLUTION

subhashpunia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mr_Shaw

ASKER

how about

WITH EXECUTE AS LOGIN = 'sa'

subhashpunia

Inside stored procedure use as: WITH EXECUTE AS 'dbo'

SOLUTION

EvilPostIt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mr_Shaw

ASKER

using dbo through back a permission error.

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

Thanks

AmmarR

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

EvilPostIt

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.

AmmarR

Sure EvilPostIt:
Agree

good day