February 11, 2008 at 1:45 pm
I have local admin rights to my SQL Servers (using a domain account specifically for doing DBA stuff, not my personal LAN account). The service account shouldn't have local admin rights (although I am sure I have read in BOL that the SQL Agent needs local admin rights in order to perform certain tasks in a particular configuration. Not a common setup though).
While it may not technically be the most secure way of doing things, it not only makes my job easier, it makes the Windows admins' life easier - I don't have to try and get some time off them to accomplish some piddling little task just because I don't have the rights. And as someone previously stated - it would be more expensive, time-consuming and frustrating to need to call the Windows admin at 3am to accomplish said piddling little task (of course, at 3am it'd probably not be so piddling, but you get the idea 🙂 ).
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 12, 2008 at 2:39 am
But perhaps you should also ponder ... should all local admins( of your server) be or have the same rights as the DBA given that by default local admins are members of the sysadmin group Hmmmm
CodeMinkey
February 12, 2008 at 3:31 am
The SQL Agent service account only needs local admin rights if you want it to automatically restart SQL Server or SQL Agent if they fail.
Getting services to restart automatically on failure can be done in a far more flexible manner using the Windows Service applet. Right-click on 'My Computer', select Manage. Open 'Services and Applications', then 'Services'. Right-click on a SQL service and select 'Properties'. Then look at the 'Recovery' tab.
IMHO it is far better to not have SQL Agent restart SQL, meaning SQL Agent does not need local admin rights, and get Windows to do this job.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 12, 2008 at 3:58 am
Andrew said:
I think thats great if you have the time, however as most have better things to do just identify what it is you need and leave it up to the person responsible to decide what that should translate into.
Sorry, Andrew, but I have to disagree.
If you think you need the rights and you're not getting them from the person responsible, then you've got no choice but to make the time in your schedule for making a list. Besides, you were given a job description when you got hired, right? That should make up the bulk of your list. Unless you're seriously off of the description you were hired for. In which case, you'd better make the time to come up with a new job description so you can get the pay commiserate to your new duties.
Regardless, it doesn't take that much time to make up a list. The harder part is verifying what rights you need to accomplish your duties.
February 13, 2008 at 10:46 am
Q (2/7/2008)
...according to my networking co-wrokers, I need to be a local admin to be able to remote into these servers.
If you are referring to RDC/Terminal Services then you really only need to be in the local "Remote Desktop Users" group. If you are referring to some other remote access application then your network guys may be correct.
Wil Moore III
February 13, 2008 at 11:11 am
One thing I say is, I dont need to be Administrator on the DBA Box or the Domain, I can work without it. But I do not want Network Admins to play around with the DB Server box without my prmission and I DO NOT want them accessing the Database at all.
-Roy
February 13, 2008 at 2:14 pm
Brandie Tarvin (2/12/2008)
Sorry, Andrew, but I have to disagree.If you think you need the rights and you're not getting them from the person responsible, then you've got no choice but to make the time in your schedule for making a list. Besides, you were given a job description when you got hired, right? That should make up the bulk of your list. Unless you're seriously off of the description you were hired for. In which case, you'd better make the time to come up with a new job description so you can get the pay commiserate to your new duties.
Regardless, it doesn't take that much time to make up a list. The harder part is verifying what rights you need to accomplish your duties.
Brandie, apologies for my scattered response but I am in a crappy mood and cant be bothered organising my thoughts 🙁
So... I think in principal we agreed. I was getting at the fact you shouldnt have to identify you need "Bypass traverse Checking" priviledge, purely the fact you need to be able to execute backups. Why waste time identifying the underlying priviledge when its more suited to someone elses position. You should be identifying "I need to perform adhoc backups of SQL Server and local file systems, restart services", etc.
I had an instance a while ago where we were originally granted Local Admin rights. But when a new site supervisor came on board she wanted to remove that on her 1st day. When I pointed out if she just revoked it we wouldnt be able to do our jobs and they would be paying us to sit around and do nothing, her response was "go and identify what permissions you need by the end of the day".
My response then was what it would be now. I will identify the tasks I need to perform, you can make it happen".
If someone wants my help, they can ask for it but dont get me to do your job for you. As someone else mention, time is already precious for most of use.
Most instances where I have taken this tact a rapid change in attitude is achieved and they move towards seperate daily and "admin" accounts. The admin accounts are heavily audited.
Cheers
Andrew
February 14, 2008 at 3:59 am
You make a decent point, Andrew. And Admin accounts should always be heavily audited. We have S.A.M. on our servers which actually makes us enter reasons we logged into the machines before we can proceed. These reasons even get logged into the Event Viewer. I'm also pretty sure someone at Corporate looks at those logs every day.
Part of my point earlier, though, was that the original poster might be asking for something he actually doesn't need. That's why I told him he needs to find out what permissions he needs to accomplish his tasks. He's asking us to justify why he should get local admin rights instead of justifying to other people why he absolutely needs it. That's the wrong approach, IMHO. If he can't articulate to us or his network people why he needs those permissions, if he has to ask us give him a laundry list that he can take back to his network admin, then maybe he doesn't truly need it at all.
The only way he'll ever know for sure, without having to ask for the laundry list, is to figure out what it is that he's doing that truly requires local admin rights and can't be done with any other permission.
Does that make sense?
February 14, 2008 at 5:50 am
Brandie, What you say makes sense. My only issue is that there are some IT Managers who think that the System Adminstrators should be able to maintain and take care of the SQL Server and also be able to deploy scripts and procedures.
That is a definite NO NO for me. Here where I am working, we (the DBAs) do not allow any scripts or procedures to go to the production DB without us reviewing it. After all it is us who will be running after issues that might arise from a badly written stored Proc.
Also we (The DBAs) are responsible for the DB. If something happens, it is not the System Administrators who are going to fix that issue. The DB might come down because of some stupidity done by the System Admin. But we are blamed for it. We had that issue once and from that day onwards we have banned the System Adminstrators from touching our DB Servers.
-Roy
February 14, 2008 at 7:54 am
And if your System Admin try to request DB access, you need to point out to your boss the concept of "Separation of duties" and all those wonderful data breaches that have occurred across the world due to someone getting access to private information that they shouldn't have had access to. (In the United States, you can quote SOX, HIPAA and other privacy laws at the boss).
Unfortunately, this argument also bites the DBA in the butt when he/she asks for local admin access on the Server that SQL is sitting on. If you're going to argue that the System Admin can't have sysadmin access in SQL Server, you can't argue that you have the same rights in the OS. Or if you do argue the point, make sure you have a lot of valid documentation to back you up.
February 14, 2008 at 9:36 pm
Q (2/7/2008)
I have worked in many places that simply gave me keys to the kingdom, domain admin rights. I don't mind having that responsibility, but it opens you up to unnecessary risks and possible finger pointing. In my present environment, I need to remote into database servers, thus I have local admin rights on these servers. I am not a network person, but according to my networking co-wrokers, I need to be a local admin to be able to remote into these servers.
In Windows 2000, unless you resort to a hack, this is true.
In Windows 2003, it's not. If this is what you are running, your network co-workers need to go look up the Remote Desktop Users local group. Now, if they have a GPO that prevents local logon except for admins, then they need to tweak that, too.
Strictly speaking, from a security perspective the Principle of Least Privilege should be enforced. Remote access to a Windows Server 2003 system through RDP does NOT require administrator rights.
K. Brian Kelley
@kbriankelley
February 14, 2008 at 9:40 pm
steve smith (2/7/2008)
Nicole,At a philosophical level, you may not be correct. Practically speaking, you're finding NOT having these rights is a pain in the $@%^&! and slows you down. The strongest argument you can make is $. How much is it costing your employer to NOT give you access rights? What's the downside risk of giving you access? You need to look at both pros and cons, and give a balanced argument. Talking about the downside risk of granting you what you need means that you are looking at the question objectively. Of course, you get to choose what arguments you present. But talking to your sysadmins about what they see as the risk of giving you rights may help your presentation. Be prepared for a corporate policy standing in your way.
If there's a policy objection, then you need to insist on faster and better support as a way to save your employer $ - it's a fallback position, but may be what you have to settle for.
Sometimes it's a matter of looking what needs to be accomplished and the method to accomplishing said task. For instance, I can setup a GPO setting that allows someone to shutdown a domain controller remotely. I DON'T want them logging on locally (thing privilege escalation). However, if they've never used any of the multitude of GUI shutdown tools or even psshutdown (SysInternals), they may insist the only way to do it is to log on locally and go to Start | Shutdown. Actually, that's more ineffecient than launching the shutdown command remotely because it means they have to leave the desk.
That's why I said it depends on what is expected. If what is expected require local administrator rights, that this is a no-brainer. If it doesn't, and it just means learning new methods that aren't a greal deal more inefficient, then there's no point going down that road. At the end of the day, if you don't have the rights, if something goes wrong, they can't point the finger at you.
K. Brian Kelley
@kbriankelley
February 14, 2008 at 9:46 pm
KevinBrenn (2/12/2008)
But perhaps you should also ponder ... should all local admins( of your server) be or have the same rights as the DBA given that by default local admins are members of the sysadmin group Hmmmm
The short answer is no. BUILTIN\Admininstrators can have its rights revoked (as long as the proper steps are taken before doing so) and I would argue that this should be done. Why does a domain admin who has no job responsibilities with regards to your financial application or HR system have the right to look at the data within that system with no restrictions. Exactly, they shouldn't. 🙂
And keep in mind that my primary responsibility is as the directory services administrator, so I'm not saying this as a DBA railing against the sysadmin types, but as the directory services administrator who realizes that directory services administrators have no business poking around in that stuff.
K. Brian Kelley
@kbriankelley
February 14, 2008 at 10:32 pm
In Windows 2003, it's not. If this is what you are running, your network co-workers need to go look up the Remote Desktop Users local group. Now, if they have a GPO that prevents local logon except for admins, then they need to tweak that, too.
Strictly speaking, from a security perspective the Principle of Least Privilege should be enforced. Remote access to a Windows Server 2003 system through RDP does NOT require administrator rights.
Really? I was under the impression that the default 2-user license was for local admins only, and that you could only use non-admin accounts if you had licenses. Is it a policy setting somewhere?
February 14, 2008 at 11:11 pm
The limitation to being administrators is only Windows 2000. The max 2 connections (not including a connection to the console... also a new feature of Windows Server 2003) is still maintained. I guess Microsoft realized that there are cases where folks may need to do some administrative work on a server but they won't have administrative rights over the server itself (like a DBA who has to manage SQL Server but where the org doesn't give out local Administrator rights).
Here's a KB article that details giving out rights to non-administrators:
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply