| Author |
Topic |
|
lakhva
Starting Member
22 Posts |
 Posted - 2005-08-22 : 16:40:39
|
| Hi,After our server was moved two jobs running in our sql server since last couple of years have failed. (first job was doing the back up of production database and then restoring development database every night from that back & second job was backup and shrink transaction logs) I checked job status history in sql server agent and I found these error messages.(For first Job error message):The job failed. The owner () of job Truncate Log does not have server access.(For second Job error message) : Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed. I work remotely. All I can do is play from sql server enterprise manager. How can I solve this problem? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-22 : 16:42:46
|
| Change the job owner to sa.Why do you have a job setup to truncate the transaction log? And why do you have one setup to shrink it? Both of these are not a good idea.Tara |
 |
|
|
lakhva
Starting Member
22 Posts |
Posted - 2005-08-22 : 16:48:41
|
| Hi,Owner is SA only. Also i checked in the logins. For SA login account here are the properties.It connects using sql server account. it has access to all the databases including master and it is in the system administrator group. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-22 : 16:51:06
|
| When SQL Server was installed, was the mixed mode authentication selected or did they keep it at the default of Windows Authentication?Tara |
|
|
|
lakhva
Starting Member
22 Posts |
Posted - 2005-08-22 : 16:54:21
|
| Sorry Tara. I don't know how they installed it and there is no person to provide me that information. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-22 : 16:56:09
|
| Right click on the server, go to properties, security tab. What does it show? If Windows Authentication only, then you'll probably need to change it to Mixed, restart the MSSQLSERVER service and try the job again.I'm leaving for the day and won't be back until Thursday...Tara |
|
|
|
lakhva
Starting Member
22 Posts |
Posted - 2005-08-22 : 17:00:11
|
| Hi, i checked out the properties. Its not windows authentication.It is mixed mode. So we are okay about that. Now what? |
|
|
|
lakhva
Starting Member
22 Posts |
Posted - 2005-08-23 : 13:16:30
|
| Hi,I tried changing the owner to someone else then again back to SA just to refresh the job hoping it may help. Now something weird is happening. Job is still failing as usual but in the view job status history i'm not seeing any entry or detailed for failed job. Seems like sql server agent notification is also screwed up. What exactly is going on here? |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-08-24 : 05:16:42
|
check the following:1. do you have builtin/administrator in your logins? - my guess is no or this account have been restricted, by default, it should have SA account - though i recommend you drop this login2. if no for #1, check if the account that runs the sqlserveragent, it should have SA privilege in the server and is a member of the local admin groupfrom hereon, it's pretty much specific, so we need specific error messages if you want more help...  HTH--------------------keeping it simple... |
|
|
|
lakhva
Starting Member
22 Posts |
Posted - 2005-08-26 : 15:19:12
|
| i work remotely and have access to sql server through enterprise manager. Jen you mentioned that .. if the account that runs the sqlserveragent, it should have SA privilege in the server and is a member of the local admin group" Can i check it remotely? How can i find it out?Also SQL goddess Tara, I hope you are back again. I will also appreciate your feeback as well. |
|
|
|
|
Job failed