SQL Agent Owner does not have access

Question

I am using SSMS 2008 R2 to run a SQL Agent job.  This job executes this stored proc below.  This stored proc works if I run it manually.  However, when I set it to run automatically overnight, it fails. 

CREATE Procedure [dbo].[sp_RefreshAllViews]
as
begin
declare @viewName sysName
declare cViews cursor for
select Object_Name(o.id)
from sysObjects o
where OBJECTPROPERTY(o.id, N'IsView') = 1
order by Object_Name(o.id)

set nocount on

-- loop over all Database views (in current database)
open cViews
fetch next from cViews into @viewName

while @@fetch_status = 0
begin

   -- exec refresh on view def.
   exec sp_refreshView @viewName
   print 'Refreshing View ' + @viewName + '...'
   -- go to next view definition
   fetch next from cViews into @viewName

end

-- close and deallocate cursor
close cViews
deallocate cViews
set nocount off
end

This was the error message:

Date        8/10/2012 9:00:00 AM
Log        Job History (RefreshAllViews)

Step ID        0
Server        EVOLVDEVDB
Job Name        RefreshAllViews
Step Name        (Job outcome)
Duration        00:00:00
Sql Severity        0
Sql Message ID        0
Operator Emailed        
Operator Net sent        
Operator Paged        
Retries Attempted        0

Message
The job failed.  The owner (YOUTHNET\r15d) of job RefreshAllViews does not have server access.  NOTE: Failed to notify 'ryan refresh views status' via email.

In Job properties, the owner is listed as:

YOUTHNET\r15d

This is my Windows Authentication ID.  The only thing possibly wrong I noticed is that my one job step doesn't contain anything for the "Run As" box on the General tab. :

(This is the only step in this job).  You can also see my connection properties in the attachment above.

Even though the General tab doesn't have any options for selecting "Run As", I noticed when I switched to the Advanced tab that I could enter "Run As".  So I selected myself (YOUTHNET\r15d). 

But this still has the error listed above.  This is the Alert I created:

I really want to get this working soon!  How can I remedy this?

---

Ryan D

Answer 1

Hi Ryan, is your SQL Agent service running under an account that has permission to run this query?

Thanks,
Sam Lester (MSFT)

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 2

I believe so.  Take a look at this:

And this:

Does this answer your question?

---

Ryan D

Answer 3

Actually, I was referring to the service account that the SQL Agent service runs under.  Open up SQL Server Configuration Manager, click on SQL Server Services, and check the "Log On As" account for the SQL Server Agent service.  That is the context that the Agent jobs run under, so you'll want to make sure that account has the permission you are expecting.  For a quick test, change that account to be a sysadmin, restart the Agent service, and try the job again to see if it is a permission issue.

Thanks,
Sam Lester (MSFT)

 

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 4

Hello,

Open the Job Properties and Select the General Page. Change the Owner of the job as 'sa' and then run it manually and then automatically.

Let me know the results.

Thanks

John