June 4, 2014 at 11:45 am
Recently we had a situation when one of our developers left the company and all jobs that were owned by him started failing. As a tempoarary fix, I assigned all jobs to be owned by [sa].
But the problem is, and it was frequently discussed in SQL Server community, that we should not use sa account, and even disable or drop it. I don't go with such extreme, but I want to find the best solution, to have an account with minimum possible privileges, so it won't be abused.
I created a SQL Srever account with only msdb SQLAgentOperator/Reader/UserRole and DatabaseMailUserRole. But as I kept changing owners of jobs, they started failing. They require access to tables, views, stored procedures, so I ended up by changing them all to sa.
So what is best practice in this case? How you solve it in your environments ?
Thanks
June 4, 2014 at 11:48 am
There are several views on this. I believe you can have the sa account disabled and still have it own jobs.
My recommendation is to either use sa or create a login that is used just to own jobs. Obviously if you are have a windows only installation that login will have to tie to a windows account as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 4, 2014 at 12:00 pm
Thanks for recommendations Jack, but I have couiple of questions:
1. How I can disable sa account? There is no such menu item in SSMS. Or should I do: ALTER LOGIN sa DISABLE
2.
create a login that is used just to own jobs.
Isn't is the same as I described in my initial post?
June 4, 2014 at 12:00 pm
Jack Corbett (6/4/2014)
There are several views on this. I believe you can have the sa account disabled and still have it own jobs.My recommendation is to either use sa or create a login that is used just to own jobs. Obviously if you are have a windows only installation that login will have to tie to a windows account as well.
+1
I will use windows accounts where it is documented or well known that a specific windows account is needed. Otherwise, a SQL account that I know won't disappear and suddenly cause the jobs 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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply