SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stored Procedure runs manually but fails as a job
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

johnny5
Starting Member

4 Posts

Posted - 10/08/2007 :  16:57:39  Show Profile  Reply with Quote
I have a stored procedure that syncs 2 databases. It runs fine if I go into the stored procedures and execute it manually, there are a few errors but it doesnt choke on them it completes with the desired end result. However if I create a job to run this dbo, it fails, The error log shows this:

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
10/03/2007 16:06:00,sync,Error,0,MACHINENAME,sync,(Job outcome),,The job failed. The Job was invoked by User MACHINENAME\Administrator. The last step to run was step 1 (sync).,00:00:19,0,0,,,,0
10/03/2007 16:06:00,sync,Error,1,MACHINENAME,sync,sync,,Executed as user: companyname. ... 0) aspnet Member Exists [SQLSTATE 01000] (Message 0) DNN User Exists [SQLSTATE 01000] (Message 0) Updaing Roles __noelle(5281) [SQLSTATE 01000] (Message 0) aspnet User Exists [SQLSTATE 01000] (Message 0) aspnet Member Exists [SQLSTATE 01000] (Message 0) DNN User Exists [SQLSTATE 01000] (Message 0) Updaing Roles 0(5036) [SQLSTATE 01000] (Message 0) aspnet User Exists [SQLSTATE 01000] (Message 0) aspnet Member Exists [SQLSTATE 01000] (Message 0) New DNN User [SQLSTATE 01000] (Message 0) aspnet User Exists [SQLSTATE 01000] (Message 0) aspnet Member Exists [SQLSTATE 01000] (Message 0) DNN User Exists [SQLSTATE 01000] (Message 0) Updaing Roles 0000000(3795) [SQLSTATE 01000] (Message 0) aspnet User Exists [SQLSTATE 01000] (Message 0) aspnet Member Exists [SQLSTATE 01000] (Message 0) DNN User Exists [SQLSTATE 01000] (Message 0) Updaing Roles 007(8) [SQLSTATE 01000] (Message 0) aspnet User Exists [SQLSTATE 01000] (Message 0) aspnet Member Exis... The step failed.,00:00:19,14,3621,,,,0

So the error is 3621, and the severity is 14 which as I understand it means Insufficient Permissions.

I have already tried this:
Making the Job Agent service logon as MACHINeNAME/Administrator instead of System, giving the user here (companyname) more permissions, but I am at a loss as to why this runs ok when manually executed but fails when run as a job. I have created the job by scripting the dbo "Execute to a file" and having the job run that file as a step, I have also tried copying and pasting the code from the stored procedure into the job step. It always fails for the same reason.

I must say I am very new to SQL, I am a network admin and have inherited this database and server from another client and have to get this sync task to run daily at certain intervals.

Please can anyone shed light on this issue?
Thanks,

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 10/08/2007 :  17:29:22  Show Profile  Reply with Quote
when you run it manually, you are running the job under your user context. when the job is scheduled to run, it is either running under the user context that the SQL Agent runs under (if the job owner is a member of sysadmin role). If the job owner is a regular user, then the job runs under that users context and not as a user with sysadmin privs.



-ec

Go to Top of Page

johnny5
Starting Member

4 Posts

Posted - 10/08/2007 :  18:07:06  Show Profile  Reply with Quote
that is the trail I think I am following - what is the quick and easy fix? Can I add a line to the process? Grant some different rights?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/08/2007 :  19:59:16  Show Profile  Reply with Quote
"what is the quick and easy fix?"

Make sure the job owner is your login ... ?

Should run the same as you running it manually then - I reckon!

Kristen
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 10/08/2007 :  21:12:14  Show Profile  Reply with Quote
How do you sync db in the sp? Is job owner member of sysadmin? Tried start sql agent with domain account?
Go to Top of Page

johnny5
Starting Member

4 Posts

Posted - 10/08/2007 :  21:30:36  Show Profile  Reply with Quote
I have the job owner, and the SQL agent, running as the local login which I logged into the server with, which is the domain admin.
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 10/08/2007 :  22:24:08  Show Profile  Reply with Quote
Did you try start sql agent with domain account? How do you sync db in the sp?
Go to Top of Page

johnny5
Starting Member

4 Posts

Posted - 10/09/2007 :  07:50:41  Show Profile  Reply with Quote
"How do you sync db in the sp?" - good question. I inherited this - I just know that it is "supposed" to work, and that if I dont sync it, members that request a password reset do not see the reset on their lists unless I do it. SQL agent service properties have been updated to logon using the domain admin account.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000