1

Googled enough and found some solutions for this error which did't help me. All I am trying to do is:

  1. Created a stored procedure:

    create procedure test
as
begin
    update dummytable set dummycolumn = 'abcd'
end

  2. Created a job - 'testjob' and run by the user who is the database owner.

  3. Error I am seeing:

    Message
    'EXECUTE AS USER' failed for the requested user 'xxx' in the database 'yyy'. The step failed.

  4. I tried: to reset the database owner, created a new user and scheduled the job, set the TRUSTWORTHY level of the database. Nothing helped.

Can someone help me fix this issue?

GSerg
  • 76,472
  • 17
  • 159
  • 346
Rick
  • 1,392
  • 1
  • 21
  • 52
  • Can you do this: `update dummytable set dummycolumn = 'abcd'` with that user ? – VBoka Jan 29 '20 at 12:35
  • Yes. When I run that query on a query window, it is working perfectly fine. I am also able to run couple of other jobs by the same user. Only this job is having issue. – Rick Jan 29 '20 at 13:30
  • How is that job set up? Executed as who? – GSerg Jan 29 '20 at 13:40
  • Permission to execute an SProc is separate from permission to CRUD. Make sure you have permissions and those permissions apply to your SProc. – Duston Jan 29 '20 at 15:07
  • @GSerg Job setup locally to be run within SQL. Set by the same user who is the owner of the database and also run by the same user. – Rick Jan 29 '20 at 15:52
  • @Duston Let me check the sproc permission – Rick Jan 29 '20 at 15:52
  • @Duston I granted all the execute permissions. Still no use – Rick Jan 29 '20 at 16:01
  • https://stackoverflow.com/q/20051234/11683? – GSerg Jan 29 '20 at 16:07
  • @GSerg http://blog.strictly-software.com/2009/09/database-owner-sid-recorded-in-master.html - Found this link in the ticket you mentioned. Resetting the DB owner worked!! Yaayy! – Rick Jan 29 '20 at 16:48

0 Answers0