Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Disclaimer: I am not a DBA. I am a database developer.

A DBA just sent a report to our data stewards and is planning to remove the NT AUTHORITY\SYSTEM account from the sysadmin role on a bunch of servers. (The probably violate some audit report they received).

I see a MSKB article that says not to do this.

From what I can tell reading a variety of disparate information on the web, a bunch of special services/operations (Volume Copy, Full Text Indexing, MOM, Windows Update) use this account even when the SQL Server and Agent service etc are all running under dedicated accounts.

share|improve this question

5 Answers 5

up vote 2 down vote accepted

If you guys have a change management process in place then challenge this. Make sure that they are aware (they really should be) of it and get their confirmation that this won't affect any services.

If you don't have a change management process to challenge this in then I would at least bring it to them. Hopefully there is a friendly relationship between your developers and administrators where you work and you'll be able to learn something from each other. They may know about the risks and might be able to explain to you why they're doing it and show you how they're doing it in a safe way.

share|improve this answer
    
They have plenty of change management process in place, they just don't fill us lowly users in on anything, and I think because of all the process they have, they don't tend to think about things critically. Also, the DBA who sent this to people who are basically business/functional administrators for the databases (at least the ones on our server) without clearly thinking, since this is a server role and has little to do with data stewardship/guardianship at the database level. –  Cade Roux Apr 9 '10 at 15:00
    
Then I would definitely at least try to approach them with this. I doubt they'd be so cold as to ignore you if you go to them with a legitimate concern and a Microsoft document to back it up. I'd also suggest that it might not hurt to have someone from your group be involved at the change management level. I don't know how things work for your company but our programming manager still attends change management for his team. –  Sean Howat Apr 9 '10 at 15:07
    
I have sent them the KB and raised the issue. I work in finance. I am basically THE SQL Server consultant/developer. Despite the large profitability system I have converted for them to SQL Server (and now moving to Teradata), they still refuse to treat it as a full-blown system, and I am often raising issues - but all this stuff is outsourced to some person at IBM India, so there's really no team nor any understanding of the roles or functions of the different servers. We used to call these kind of DBAs "functional DBAs" - i.e. backup/restore/security - no actual DB understanding –  Cade Roux Apr 9 '10 at 15:21
1  
Ah I see. Well it sounds like it's a bit of a tough situation for you then. I would simply keep doing my best to raise issues where you see them. Hopefully they have a good reason for what they're doing. –  Sean Howat Apr 9 '10 at 17:12

Have these people been asked why they want to remove it, and if they understand what the purpose of the System account is in the first place? I would agree with your guess about an audit report being involved here, and I would also guess that the report just listed which accounts have rights to do what, and that the DBAs are blindly following it by removing any accounts they don't recognise.

Basically the System account is used to give the OS itself rights to Do Stuff. It's not a general user account and shouldn't really be treated as such.

If the DBAs are determined to remove it, maybe try suggesting that they do so on a test system first (preferably one that gets some active day to day use), give it a month to see if anything happens, and then make a final decision.

share|improve this answer
    
I've passed on what information I have gathered. At this point, I'm too busy with my own workload to train the DBAs. –  Cade Roux Apr 13 '10 at 20:10

The only legitimate reason for them to do this (and your MS article confirms it), is to try to prevent administrators of the server SQL is running on from having admin access to the databases. The problem they'll have is that any determined administrator of the OS can go back in and add their permission back in because they have full access to the server. It sounds like what they want to do shouldn't negatively affect anything on the DB (unless something is set up to use AD accounts that are local admins and have no specific DB permissions set), but it's definitely something that they should test first like mh suggested.

If you currently have admin access to the DB through being a local admin of the OS, I'd suggest you create a SQL user (or get another AD user account) that has sysadmin access if that is possible. This would ensure you still have access if they make this change.

share|improve this answer
    
I've got no SQL Server admin or OS admin rights. In our particular database, I can create tables, views, UDFs, procs etc, but I can't even run sp_who to see if I'm blocked, so... –  Cade Roux Apr 13 '10 at 20:08

You didn't say which version, and that's key. If you're talking SQL Server 2000 and Full text is installed, no, you cannot remove it. The reason for that is if Full Text does not run under the local System account, it could potentially throw an Access Violation and crash. And the account Full Text runs under needs sysadmin access to the SQL Server. So there you go.

As of SQL Server 2005 and higher, it depends on what accounts you have your services configured to run under. Here's the Books On-line page with the information on service accounts. Generally speaking, local or domain accounts are preferred over any of the built-in accounts for the major services, thus alleviating this concern.

Setting Up WIndows Service Accounts (SQL Server 2005 Books Online)

One other thing you didn't mention is whether BUILTIN\Administrators have been removed from the sysadmin fixed server role or not. If not, then System still has sysadmin access as it is considered a member of that local security group.

share|improve this answer
    
I would think BUILTIN\Administrators has already been removed. So windows admins cannot just login as administrators. So this remaining "loophole" would mean that a windows admin could still execute something under the local system account and do things. Of course, they can bring the DB up in single user mode and get access... –  Cade Roux Apr 13 '10 at 20:02
    
This is SQL Server 2005 and neither I nor my boss who is one of the data stewards (approve database role access to databases) being asked to sign off on this change are either windows admins or database admins - and this is a server-wide role, so the individual data stewards can't really have a say. I've passed the info up the line. –  Cade Roux Apr 13 '10 at 20:05
    
Or they can add themselves to whatever group you're using for DBA access and unless you're auditing AD you won't catch 'em. Or, if it's not Windows Server 2008, they can use a DLL injection attack against LSA Secrets and dump the service account password in plaintext. Or, since you're using 2005, they can simply stop the SQL Server service and copy off the database files. So this isn't much of a defense but it tends to keep the IT auditors happy. –  K. Brian Kelley Apr 14 '10 at 3:34

If the NT AUTHORITY\SYSTEM account has the SysAdmin role in SQL Server, that means that any Windows Service, or Scheduled Task, running under LocalSystem has God-access to your SQL Server. Is that really something you want...??!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.