Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
johnny5
Starting Member
4 Posts |
 Posted - 2007-10-08 : 16:57:39
|
| 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 Attempted10/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,,,,010/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,,,,0So 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
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-10-08 : 17:29:22
|
| 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 |
 |
|
|
johnny5
Starting Member
4 Posts |
Posted - 2007-10-08 : 18:07:06
|
| 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? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-08 : 19:59:16
|
| "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 |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-08 : 21:12:14
|
| How do you sync db in the sp? Is job owner member of sysadmin? Tried start sql agent with domain account? |
|
|
|
johnny5
Starting Member
4 Posts |
Posted - 2007-10-08 : 21:30:36
|
| 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. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-08 : 22:24:08
|
| Did you try start sql agent with domain account? How do you sync db in the sp? |
|
|
|
johnny5
Starting Member
4 Posts |
Posted - 2007-10-09 : 07:50:41
|
| "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. |
|
|
|
|
|
|
|
|
Stored Procedure runs manually but fails as a job