December 30, 2009 at 8:23 am
Interesting question. I use sa and, interestingly enough, Tibor Karaszi has a blog post about this exact question today. Check it out.
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
December 30, 2009 at 8:32 am
I prefer to create a domain account for that, domain\SQLBackups. Give it rights to the backup directories, give it rights in SQL, and be done with it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 30, 2009 at 8:42 am
Jack Corbett (12/30/2009)
Interesting question. I use sa and, interestingly enough, Tibor Karaszi has a blog post about this exact question today. Check it out.
Errr... I'm pretty sure he put that post on his blog after reading my question! His answers are, ummm... unhelpful.
Look, SQL gives us access control right down to the column-level. Then I hear many? most? DBA's say, "just use sa". Really? Why, exactly, did Microsoft bother to create a db_backupoperator Database Role? Does anyone ever use this thing? Or any of the other security features of SQL?
Or does every admin just add all users in their domain to the Domain Admins group, so everyone has God Rights to everything. Everything *will* work (including the hacking of your data, and the unmitigated spread of viruses, etc...)
December 30, 2009 at 8:52 am
subs99 (12/30/2009)
Jack Corbett (12/30/2009)
Interesting question. I use sa and, interestingly enough, Tibor Karaszi has a blog post about this exact question today. Check it out.Errr... I'm pretty sure he put that post on his blog after reading my question! His answers are, ummm... unhelpful.
Look, SQL gives us access control right down to the column-level. Then I hear many? most? DBA's say, "just use sa". Really? Why, exactly, did Microsoft bother to create a db_backupoperator Database Role? Does anyone ever use this thing? Or any of the other security features of SQL?
Or does every admin just add all users in their domain to the Domain Admins group, so everyone has God Rights to everything. Everything *will* work (including the hacking of your data, and the unmitigated spread of viruses, etc...)
Of course!
But seriously, why not do what I suggested?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 30, 2009 at 8:56 am
I agree with setting a domain account for these backup jobs, or you could set up a local server windows account to do it also.
If you do use SQL Server security (logins) to do this create specific logins to use besides SA.
The probability of survival is inversely proportional to the angle of arrival.
December 30, 2009 at 9:07 am
GSquared (12/30/2009)
I prefer to create a domain account for that, domain\SQLBackups. Give it rights to the backup directories, give it rights in SQL, and be done with it.
OK, I think this may be the correct way to do it.
But what AD group do you add it to, or just leave it in the Users group?
When you create a SQL Login with this account, what Server Role(s) do you add it to?
Do you have to then give it Database Role(s) in each database within an instance? db_backupoperator?
Do you have to go through each backup directory and give the Windows Login permissions to that? What are the least permissions you give it?
What, if anything, happens if you change this domain account's password?
Thanx!!
December 30, 2009 at 10:03 am
subs99 (12/30/2009)
Errr... I'm pretty sure he put that post on his blog after reading my question! His answers are, ummm... unhelpful.Look, SQL gives us access control right down to the column-level. Then I hear many? most? DBA's say, "just use sa". Really? Why, exactly, did Microsoft bother to create a db_backupoperator Database Role? Does anyone ever use this thing? Or any of the other security features of SQL?
Or does every admin just add all users in their domain to the Domain Admins group, so everyone has God Rights to everything. Everything *will* work (including the hacking of your data, and the unmitigated spread of viruses, etc...)
I'm not sure where you have worked that DBA's say "just use sa" as I've yet to run into one who says that. The db_backupoperator Database Role is there so you can allow users to run backups of specific databases without needing sa access. This role is rarely, if ever used, because most DBA's schedule backups and don't want users taking backups because it will break the backup chain (unless you use copy only, but how many users know about that?). K. Brian Kelley has a good write up on the Fixed Database Roles over a MSSQLTips where he gives several reasons why this role is usually not used.
Using sa as a job owner isn't, in my opinion, a security risk. In most cases only users with sysadmin privileges can create jobs so they can already do anything on the server. Yes, starting with SQL Server 2005 there are Agent Roles, but I'd bet those are rarely used.
I think you misunderstood what Tibor is saying. He's saying using sa can help avoid problems and be fine security wise because:
...we can change password for sa, disable sa, or even run in Windows Only mode.
And jobs will still run. Thus you can just disable the sa account or change the password as often as you want without affecting the status/runnability of the job(s).
I'm not saying you have to go that way, I'm just saying that I don't really see a security risk involved with this as you still control who can create, view and run jobs and those in the SQLAgent Roles cannot change the owner to sa only sysadmins can.
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
Viewing 7 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply