March 30, 2009 at 12:32 pm
Philip,
I believe I understand why you're having this problem. I'll take a stab at explaining.
When you create your maintenance plan, SQL server creates a job to execute that plan. This (new) job calls the "xp_sqlmaint" procedure (in the master database) with a bunch of parameters (e.g., maintenance plan ID, backup directory, etc.). That procedure, xp_sqlmaint, is calling the "sqlmaint" utility with your parameters; the sqlmaint utility installs with SQL Server and is used to run maintenance plans. If you don't specify a context to run sqlmaint under, it inherits the context of whatever executed it. In the case, that will be the accounting running the SQL Server service, which in many cases is (the) "Local system account" a.k.a "NT AUTHORITY\SYSTEM". That account is (I believe) a member of the local administrator group. If you've whacked access for the local admins, "NT AUTHORITY\SYSTEM" isn't going to be able to login to the local SQL instance, and it ALWAYS needs to login for one reason or another. If you copy and past the text of the job and run it from query analyzer (or SSMS), you'll see the same "NT Authority" mumbo jumbo because sqlmaint is attempting to run under SQL Server's context, but that account doesn't have the needed access to msdb.
I'm a SQL Server newbie so take this with a grain (pound) of salt 🙂
Regards,
Adam
Adam
March 30, 2009 at 6:04 pm
Adam,
That was one job, four years, and a SQL Server version ago, and I really don't recall the details. It sounds like you're working with SQL 205, and I am pretty sure that there were enough "low-level" changes made to SQL, security, and maintenance plans in going from 2000 to 2005 to make the systems incompatibly incomparable. If that's a valid phrase.
In any case, I abaondoned maintenance plans when I moved to 2005 because they failed to perform simple reindexing routines on the system databases (I mean, duh!); I rolled my own system, and haven't looked back since. Did they ever debug them?
Philip
March 31, 2009 at 7:51 am
Ha! Wow, I just realize that your post was from 2005. Goodess, I really need some coffee.
No, I'm sad to say I've been doing a lot of work with 2000; although, 2005 is on the horizon. My organization is on the dull rusty edge of database technology.
Whelp, hopefully that post will help someone some where also on the dull rusty edge.
Cheers,
Adam
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply