Friday, August 10, 2012 2:04 PM
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
Job Name RefreshAllViews
Step Name (Job outcome)
Sql Severity 0
Sql Message ID 0
Operator Net sent
Retries Attempted 0
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:
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?
Friday, August 10, 2012 2:19 PMModerator
Hi Ryan, is your SQL Agent service running under an account that has permission to run this query?
Sam Lester (MSFT)
Friday, August 10, 2012 3:12 PM
I believe so. Take a look at this:
Does this answer your question?
Friday, August 10, 2012 3:22 PMModerator
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.
Sam Lester (MSFT)
Tuesday, August 14, 2012 11:39 PM
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.