June 25, 2009 at 4:14 am
hi to all,
i have a job to archive data from the production to another log database(600 tables). for this purpose i have created the statements for each and every table like:
insert into DBLog.dbo.logtabl1
select * from DBPROD.dbo.um_tbl1
go
truncate table dbprod.dbo.um_tbl1
like this i have created for the 600 tables and make it as a job.
but it is giving error:
Executed as user: NT AUTHORITY\SYSTEM.
Incorrect syntax near 'um_'. [SQLSTATE 42000] (Error 102). The step failed.
please help me.
🙂
June 25, 2009 at 4:51 am
HI
What is your sql service starting up as ?
You need to start up with a domain account , or put back built in admins back into sql server not recommended , security breach
regards
KP
June 25, 2009 at 5:21 am
i didn't understand, could please explain.
🙂
June 25, 2009 at 5:38 am
When I run the script from Query Analyser it works fine. But when I schedule the same script to run as a job, it fails and i get the following error:
Executed as user: NT AUTHORITY\SYSTEM.
Incorrect syntax near 'um_'. [SQLSTATE 42000] (Error 102). The step failed.
please help me out.
🙂
June 25, 2009 at 5:46 am
HI
Click start and run type : Services.msc
Scroll down to sql server (Name) what does that start with ?
Whe youi execute the query you are running it as yourself , when ot is scheduled you are running as the SQL service account
June 25, 2009 at 7:04 am
SERVER PRODUT DETAILS
ARE
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Is there any problem with this version.
🙂
June 25, 2009 at 8:19 am
try this:
goto Job properties, check the owner and change it to SA or use a windows account with higher priviliges.
Also can you let us know the following:
start services in Start-->Control Panel-->administrative Tools then click on services and check to see SQL Server Agent Service and let us know, what it says under "log on as" column
Maninder
www.dbanation.com
June 25, 2009 at 7:30 pm
is this really issue with the owner? i have other jobs like backups, it's working fine.
🙂
June 25, 2009 at 7:38 pm
i've checked with ur mentioned path,
for the agent log on as : local system account,
job owner is sa .
🙂
June 25, 2009 at 8:08 pm
Make sure NT AUTHORITY\SYSTEM has a SQL Login and rights to both databases being accessed. If the service account for SQL Server is NT AUTHORITY\SYSTEM (AKA LocalSystem), it should already have a login with sysadmin rights.
It could be that SQL Server & SQL Agent have different service accounts. Possibly SQL Agent uses NT AUTHORITY\SYSTEM and SQL Server uses something else. As stated above, make sure there is a login for NT AUTHORITY\SYSTEM and it has appropriate rights assigned.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply