March 21, 2003 at 1:49 pm
I am scratching my head on this one. I log into my sql server database as a W2K administrator and have it set up to do anything and I can't backup any databases. I am using the wizard as what the wizard does works for what I need(using enterprise manager). Something happened when my network admin rebuilt the servers and really screwed up my logins on sql server. I fixed everything else I think now I am stumped on trying to backup. Any suggestions?
oh the error message I get is:
Executed as user: ARLNET\Administrator. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
which basically says I do not have permission. very bizzare.
Matt
March 21, 2003 at 2:29 pm
Double click your maintenance plan --> Reporting Tab --> Check 'Write report to a text file' option to log the execution of the maintance plan job. Review the log file for more information why your backup job failure.
March 21, 2003 at 2:38 pm
should be in your logs folder, near where your data folder is.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 21, 2003 at 3:11 pm
hmm very interesting error message:
Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC SQLState: 28000)] Error 18456: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\SYSTEM'.
Any ideas? I am wondering if it has anything to do with deleting Builtin/Administrators.
Matt
March 21, 2003 at 3:39 pm
Did you remove BUILTIN\Administrators login from SQL Server? Which account do you use to run SQL Server and Agent services?
I believe SQL Server Agentg is running by local system account. If it is, you have to grant 'NT Authority\System' to access SQL Server with 'sa' previlege.
March 24, 2003 at 7:07 am
And how do I check that? All of SQL Services can start just fine, I just can not start my backups. I do not have an NT AUTHORITY\SYSTEM login.
Matt
March 24, 2003 at 7:28 am
Goto service to verify which account is used to start MSSQLServer and SQLServerAgent services.
USE master
go
EXEC sp_grantlogin 'NT Authority\System'
go
EXEC sp_addsrvrolemember 'NT Authority\System', 'sysadmin'
go
Edited by - Allen_Cui on 03/24/2003 07:33:46 AM
March 24, 2003 at 7:49 am
I fixed it for the time being. The SQL Server Agent WON'T start under local so I used admin and gave it sa priviledges.
Matt
March 24, 2003 at 8:00 am
Suggest to review machine application and system logs why SQLServerAgent service was unable to start under local system account.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply