Server Administrator Permissions
-
The problem with applying things through GPOs is that while many DBAs may have local Windows Admin permissions, rarely --if ever-- does a DBA have Domain Admin permissions or Admin permissions on an AD / Domain Controller box. Let alone permissions to set up and run a GPO throughout the system.
So, don't use local Windows Admin permissions to set up GPOs on the box unless this is the only SQL Server you have because multiple GPOs throughout the domain can contradict each other and one set up higher on the food chain (so to speak) can kill any settings you put on a lower box.
Does that make sense?
-
If we are talking about a dedicated SQL Server, why should the dba not have admin permissions?
If i am responsible for an SQL Server and its performance i do want to have complete access to it, i want to know all installed software, i want to be able to have a look at drivers, services, processes, hardware...
Of those admins i know usually the dba's know a lot about windows administration, but the windows admins usually know virtually nothing about databases. So whom would i like to have as an windows admin (not domain admin) of a database server?
karl
Best regards
karl -
Brandie,
you are right, this is something that most DBAs wouldn't be doing. However, this is certainly a great prompt to the GPO/directory services administrators. Those guys are also supposed to be managing conflicts with GPOs (and there are a multitude of great tools which can show conflicts).
From an enterprise perspective, appropriate GPOs and/or use of security templates are the right way to go to ensure a consistent configuration across all the SQL Servers. This is certainly something DBAs should be interested in and talking with the domain admins about (if they aren't the domain admins).
K. Brian Kelley
@kbriankelley -
Why shouldn't they? The Principle of Least Privilege is why. If the DBAs aren't expected to be responsible administratively for a server, they shouldn't have those rights. This just isn't true of DBAs, mind you. It's a general principle that applies to everyone. Now, if the DBAs are responsible for managing the server, then the principle would apply saying they need those rights.
As for your anecdotal view, mine is that typically a lot of the DBAs I've come across don't know a whole lot outside of SQL Server and the bulk of sysadmins don't know a whole lot about SQL Server, so both are operating from a standpoint of ignorance. However, my view is also anecdotal and doesn't speak to the knowledge set in a particular organization. I've been in organizations where every sysadmin was qualified to be a SQL Server DBA (at least the production side). So this is really an organization by organization decision.
K. Brian Kelley
@kbriankelley -
I would simply say because its Windows. Have you ever tried to run Windows XP without being an admiin? Its pretty damn hard thanks to the way MS designed OS. On our Oracle Unix boxes we don't have root access -- don't need it. I would also say MS SQL is very tightly integrated with Windows OS, and even if your own login doesn't have admin access the service account should, sure you can try and remove the service account from local admin, but good luck making sure everything works correctly (did you notice how builtin\administrators is still sa in 2005). With that said, if your service account has local admin access it does little good to prevent your own account from having local admin access sine you can just use the service account anyways.
-
But the last time I checked, you couldn't really log into the actual server (OS) with the SA account, even though the SA account has those privledges. You have to go through SQL Server to use the SA account and even though the account has those privledges, it can't access everything on the OS. SQL Server just doesn't have the toolset to get access to every last item on the OS despite the inflated access.
Also, SA is only included for backward compatibility. According to my documentation, it can be disabled. And I don't recall that it comes up as a default option during installation. You actually have to click it to activate the SA account. (Of course, it's been nearly a year since I installed 2005, so I could be misremembering).
-
At the end I think that the most important thing is to check what the DBA has to do on the server and if he has the required skills. In our organisation for instance we are system engineers specialised on SQL Server installation and configuration. Further we make 3rd level support and performance tests if there are some problems. We need as well a comprehensive understandig of the system. This is the reason because most of us are MCSE. So we have to have admin rights, but not domain admin rights.
You are concerned about the managing of the system, but what about the data ? Perhaps a DBA has access to data, maybe sensitive informations.
-
Actually, unless you're installing things all the time, running with a normal user account in Windows XP is quite possible. Not just for the home user, but also for the business user. With few exceptions, most applications can be made to work with just a little investigation with process monitor (which combined filemon and regmon into one product). This is something SBS MVP Susan Bradley has virtually led a crusade to see happen.
As far as making things working correctly on the SQL Server side, it really depends on your functionality. There are some things you'll lose if you don't run as a local admin (and Microsoft documents those), but it's very, very do-able. And if you don't know the service account password, short of resorting xp_cmdshell (which can be dropped in 2000), you're limited in your ability to execute in the context of the service account.
K. Brian Kelley
@kbriankelley -
I think he's referring to the service account the SQL Server service actually runs under. If you know its password and it is granted administrative rights over the system, you still effectively have the capability to log on as an administrator. Some organizations carefully control the passwords to the service accounts because without doing so, you lose non-repudiation.
K. Brian Kelley
@kbriankelley -
Real life example of a reason why. Doing a migration from SQL 7 --> 2k5.
What to do first? use the 2k5 tool Upgrade Advisor. Attempting to run it from my workstation against a production server, to ensure that ALL features are looked at. Except that UA cannot access the production registry to connect to the database I want analyzed. I don't have the rights, without having sysadmin privileges on the production machine (which is the only SQL 7 instance we have).
Justifies temporary permission, but not permanent access privileges. And only barely justifies temporary permission, but it's a live example.
[Thanks to Brandie for spotting my poor terminology. I meant Windows Admin rights, or that portion that allows read/write to the registry. 20 July 2007 10:29 BB time]
-
Did you really mean to say SysAdmin?
I ask because the original post was about Local Admin Permissions on the Windows OS (Administrator rights) which isn't exactly the same thing as SQL Server SysAdmin.
-
SysAdmin = abbreviation for System Administrator. This is actually where Sybase/SQL Server got it from. For instance, SysAdmin magazine. I should have been more clear, but usually when I refer to sysadmin as in SQL Server's role, I write sysadmin fixed server role.
K. Brian Kelley
@kbriankelley -
Sorry, I should have mentioned that my last comment was for Steve. It seems odd to me that the Upgrade Advisor wouldn't work if you didn't have Windows Admin permissions.
Has anyone else run into that problem?
-
I didn't say it wasn't possible its just hard to do and as you indicated spending time fiddling around with Process Mon and Reg Mon to make it work doesn't sound easy. My point with the Unix/Oracle comment is simply this most Oracle DBAs do not have root access whereas most SQL Server DBAs have local administration access. When my company floated around the same question, i.e. do the DBAs need to have local admin access my response began with the concession, "Well I could see your point if we were running Unix, but on the Windows platform where SQL Server is tightly integrated with the OS and the DBA often need to do things which unfortunatley require admin access...."
-
I disagree with respect to processmon because we're comparing apps that run on workstations versus servers. Quite simply, just about everything a DBA would need to do (if looked at from an Oracle on Unix perspective) can be done by a SQL Server DBA without administrative rights.
If you're on a Windows Server 2003 that includes using PerfMon because there are two local groups specifically designed to be able to do performance monitoring and establishing logs. And as far as starting and stopping SQL Server, is it typical for a non-privileged user to be able to stop and start processes on a Unix box which are like Windows services? Not really.
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply