April 4, 2011 at 8:24 pm
I got some backup job of maitenance plan failed.
I found the job is running an account under a dba who just left us. So the network engineer either diable her account or removed her from DBA group.
The failed job error message is:
The owner of job maintenanceplan.differential backup doesn't have server access.
I thought job is running under sql server agent account.
So does the owner of the job matters? Why it complains the owner of job doesn't have server access?
Also if change the job owner to my account in SSMS, job property screen, will that work?
I have sysadmin rights on the server/
April 4, 2011 at 10:27 pm
The owner of the job does matter. If an owner is set to something other than the default, then that person must have appropriate rights in SQL Server and NTFS rights if the job accesses the file system.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2011 at 9:08 am
But isn't it the job running under sql service account which is sysadmin, as long as service account has access to NTFS or folders, it should be OK, so why job owner matters?
April 5, 2011 at 9:13 am
Because when you actually specify a job owner, the job will execute as that user and not the service account.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2011 at 9:14 am
This is part of the reason why setting service accounts or job ownership to specific users is not necessarily a best practice. When their account is disabled or expires then processes start to fail.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2011 at 9:32 am
I now got confused, I always have an impression SQL jobs running under sql service agent account, that's why the agent service account has to be a sysadmin, so it has access to some folders.
Is there anywhere in SQL book online or any other articles talking about this?
I mean about Sql Job running under whose account?
sql server agent service account or Job owner account?
Thanks
April 5, 2011 at 9:50 am
Don't get sysadmin confused with other types of admins. Just because an account is sysadmin in SQL server does not mean that account has the appropriate NTFS permissions for anything outside of SQL server. Those two are managed entirely separately.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2011 at 9:54 am
Thanks.
Also can anyone help answer my questions in my previous post?
April 5, 2011 at 10:08 am
Here is one reference.
http://weblogs.sqlteam.com/dmauri/articles/6914.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2011 at 11:07 am
Thank you, this is helpful.
Hope it can be explain in SQL books on line.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply