February 18, 2010 at 10:29 am
As I mentioned yesterday, our system admin and dba resigned recently, and now I'm doing the dba activities. I've been reviewing the maintenance plan that he set up, and have created a subplan to do a full backup of the master, model and msdb databases. I'll verify that they work when next they run.
The question I've got now is what account should such jobs run under? When I look under Maintenance Plans it doesn't should any account information, but I've noticed that if I look under SQL Server Agent | Jobs I'll see the account that each job runs under. What I've found is that each job in the former dba's maintenance plan, runs under his Windows account. Well, we've disabled his account, so how's that work? (I would assume it doesn't, or at least it won't, for long.) Wouldn't it be better to have these jobs run under the sa account?
Kindest Regards, Rod Connect with me on LinkedIn.
February 18, 2010 at 12:30 pm
I'd probably choose a non-user account. Create one specifically for jobs on that instance to run under.
February 18, 2010 at 12:34 pm
Don't maintenance plan jobs run as the SQL Agent service account like other jobs even if they're owned by another login? I don't use maintenance plans much so I'm not that familiar with the security requirements.
Greg
February 18, 2010 at 12:40 pm
They can run under the Agent service account (default behavior), when run as a scheduled job.
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
February 18, 2010 at 12:47 pm
I have found that the only time the owner of a maintenance plan (or Agent job) has any issues is when that owner account is a domain user account. When that account is disabled, nothing bad happens because it can still be found in AD. However, once that account is removed from AD or the account is removed from the group memberships that are used to validate the user - the jobs will fail.
A good example would be a DBAdmin group. The DBAdmin group is setup as a login in SQL Server and the user is a member of that group. Now, the member's AD account is disabled - no problem. Then, the member is removed from the AD group - jobs will fail because that user is no longer authorized.
I have found that setting the ownership to the sa account is the easiest way to manage this. The owner does not have anything to do with the permissions used when the job runs - that is going to be either the user that runs SQL Server Agent, or a proxy account that is setup for that type of job.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 18, 2010 at 4:18 pm
You should never use the sys admin account for anything. That is only for backwards compatibility. Not sure why it is even still in here.
Create a simple SQL user account and use that. If you want to place any files off the box, onto a different box, then you need a domain account to run the service on. The SQL Service. Then give that domain account write permissions on that directory, where ever it may reside.
You can create any account you want in SQL, create a very low permission account and use it. NEVER use the sys admin account. You are only asking for big trouble.
Andrew SQLDBA
February 18, 2010 at 7:40 pm
AndrewSQLDBA (2/18/2010)
You should never use the sys admin account for anything. That is only for backwards compatibility. Not sure why it is even still in here.Create a simple SQL user account and use that. If you want to place any files off the box, onto a different box, then you need a domain account to run the service on. The SQL Service. Then give that domain account write permissions on that directory, where ever it may reside.
You can create any account you want in SQL, create a very low permission account and use it. NEVER use the sys admin account. You are only asking for big trouble.
Andrew SQLDBA
I am not sure where you are getting this information, since all we are talking about is who owns the agent job and not the user that is running it.
Setting the owner of the job to 'sa' works very well, and will work if you have your system setup for Windows Authentication only.
This is the same concept as setting the database owner to the sa account. This does not grant any additional privileges or permissions to the database - nor does it grant any additional privileges to the agent job. Remember, the agent job does not run in the context of the owner of the job - it runs in the context of the user that is running SQL Server Agent, or in the context of the proxy user account that you have defined for that type of job - or the specific user if you set a specific user for the job.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 18, 2010 at 8:00 pm
I would prefer to run under same domain account which is used for SQL services.
EnjoY!
February 18, 2010 at 8:46 pm
AndrewSQLDBA (2/18/2010)
You should never use the sys admin account for anything. That is only for backwards compatibility. Not sure why it is even still in here.Create a simple SQL user account and use that. If you want to place any files off the box, onto a different box, then you need a domain account to run the service on. The SQL Service. Then give that domain account write permissions on that directory, where ever it may reside.
You can create any account you want in SQL, create a very low permission account and use it. NEVER use the sys admin account. You are only asking for big trouble.
Andrew SQLDBA
I wouldn't say "never."
You should avoid logging in as SA. Could you provide documentation on SA being only for backwards compatibility?
I agree that logging in as SA, you could be asking for big trouble. However, job ownership and database ownership do not cause a login. Job execution on the other hand is handled differently.
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
February 18, 2010 at 9:10 pm
I am with Jason on this, most of the time jobs are run under 'sa' or SQL services account, unless you want some else (other than DBA's) is running job manually you set up account with restricted permission.
EnjoY!
February 19, 2010 at 5:20 am
I always drop the "sa" account, and re-create it and assign no permissions and assign it to public group
SQL BOL
System Administrator (sa) Login
System administrator (sa) is a special login provided for backward compatibility. By default, it is assigned to the sysadmin fixed server role and cannot be changed. Although sa is a built-in administrator login, do not use it routinely. Instead, make system administrators members of the sysadmin fixed server role, and have them log on using their own logins. Use sa only when there is no other way to log in to an instance of Microsoft® SQL Server™ (for example, when other system administrators are unavailable or have forgotten their passwords).
There is your reference
Andrew SQLDBA
February 19, 2010 at 8:26 am
AndrewSQLDBA (2/19/2010)
I always drop the "sa" account, and re-create it and assign no permissions and assign it to public groupSQL BOL
System Administrator (sa) Login
System administrator (sa) is a special login provided for backward compatibility. By default, it is assigned to the sysadmin fixed server role and cannot be changed. Although sa is a built-in administrator login, do not use it routinely. Instead, make system administrators members of the sysadmin fixed server role, and have them log on using their own logins. Use sa only when there is no other way to log in to an instance of Microsoft® SQL Server™ (for example, when other system administrators are unavailable or have forgotten their passwords).
There is your reference
Andrew SQLDBA
And that is talking specifically about logging in using that account. Which I agree - you should not login to SQL Server using that account.
That has absolutely nothing to do with who owns objects in the database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 19, 2010 at 10:59 am
The backward compatibility in this scenario is "SQL Authentication." If you never use SQL Authentication, you would not need to use SA either. MS is trying to have people use Windows Authentication - which is more secure. If you eliminate the backwards compatibility - there would be no need to drop the SA account and create one to replace it.
However, there is usefulness in a SQL Authentication account. As Jeff describes - to own a job or database would be one such use. Nobody would be logging on with the account, and there is less likelihood that the account will get dropped and create further repercussions in the 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
February 19, 2010 at 6:43 pm
Thank you Jason - couldn't have said it better myself...in fact, I didn't 🙂
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply