THE SQL Server Blog Spot on the Web
Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search
Home Blogs Downloads   Opml View as RSS news feed in XML

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Question: Is NT AUTHORITY\SYSTEM a sysadmin in your SQL Server and Why?

I asked this on twitter and now I am going to ask it on here to see what kind of responses I get.  If your SQL Server services run under a domain user account, is NT AUTHORITY\SYSTEM (Local System) a sysadmin in your SQL Server and if so, why?

I was prompted to ask this question while configuring a server early today and after installing SQL Server 2008 on Windows Server 2008 R2, I noticed that despite being installed from default using a Domain Account for the Services, the Local System account was still a sysadmin in SQL Server.  In the past I have removed this account from my servers, and I did so today, but the thought crossed my mind, “What might I be breaking by doing this?”

Here is what I don’t want. 

  1. I don’t want responses that can’t be backed up with documentation or proof of the need.  I am trying to find out the hard facts about why this account would have been left as a sysadmin in a fresh install of SQL Server and guessing just won’t cut it for this.
  2. I don’t want stories about why SQL can be run as Local System, that’s not the point of this post or the question being asked.

What I do want:

  1. Documented cases where Local System is needed.  For example K Brian Kelley(Blog/Twitter) offered that Full Text in 2000 required Local System to be a sysadmin in SQL Server 2000 if BuiltIn\Administrators had been removed from SQL Server.
  2. Changes required if the Local System is removed.
  3. Anything else pertinent to this account existing in SQL Server 2008.
Published Wednesday, January 27, 2010 10:10 PM by Jonathan Kehayias

Comments

 

josantia said:

Hi Jonathan,

In my case, I never remove this accout from sysadmin role.

I found this Microsoft article: http://support.microsoft.com/kb/919023 saying explicitely that they don´t recommend doing so.

Regards,

José Santiago Oyervides.

January 28, 2010 1:34 AM
 

Jonathan Kehayias said:

Jose,

That would only apply if you were using a VSS based backup solution, and you could change the startup account for the SQL Writer service from Local System to another account that had acceess to SQL to mitigate against the need for Local System to have access to SQL in that case.  If you use native backups, there is no need for Local System to be there for VSS.

January 28, 2010 1:44 AM
 

GrumpyOldDBA said:

I've been having the same discussion with our data centre, who build my servers and we've not really got too far in this respect to a definitive answer, however in my case they are running the cluster account under this account ( ! ) and I did discover that in this case the account requires permissions to execute select @@servername. I removed sysadmin rights and granted appropriate rights to make this call and my cluster works fine. On my standalone servers I have removed the permissions and everyhting works fine. The data centre is a managed service so they're not always very forthcoming - I did find they were running their sql monitoring using this account which required sysadmin - I encouraged them to use a proper account. I'd be very grateful for a clarification on this matter too.

January 28, 2010 2:53 AM
 

GrumpyOldDBA said:

sorry - my reference is "Pro SQL Server 2008 Failover Clustering" by Allan Hirt.

January 28, 2010 4:56 AM
 

Dave Wentzel said:

We have an application that requires SQLExpress to be installed on users' laptops.  NETWORK SERVICE worked well for us except 1)when we enabled FILESTREAM we just couldn't get it to work correctly.  The reasons are documented elsewhere on the web, can't find the links now. Of course LocalSystem isn't needed to overcome this specifically but 2)our users generate various SSPI errors in situations when SQLExpress runs as a domain acct or a local user acct and the machine is a domain member,undocked, and on one of our many flavors of VPN.  

January 28, 2010 9:38 AM
 

Jack Corbett said:

Interesting question.  I just checked my servers and the account has access and is sysadmin.  I don't know why and I haven't changed it.  I'm on SQL Server 2005.  It is definitely something I need to look into.  What are the risks with leaving it as sysadmin?

January 28, 2010 10:29 AM
 

steve said:

i never removed the account as i didnt see a need to do so but i found this article

http://support.microsoft.com/kb/932881

The NT AUTHORITY\SYSTEM account

The NT AUTHORITY\SYSTEM account is also granted a SQL Server login. The NT AUTHORITY\SYSTEM account is provisioned in the SYSADMIN fixed server role. Do not delete this account or remove it from the SYSADMIN fixed server role. The NTAUTHORITY\SYSTEM account is used by Microsoft Update and by Microsoft SMS to apply service packs and hotfixes to a SQL Server 2005 installation. The NTAUTHORITY\SYSTEM account is also used by the SQL Writer Service.

January 28, 2010 1:33 PM
 

Alan Cranfield said:

our standard build scripts always remove this account from all our SQL Servers here at NYSE Euronext

January 29, 2010 4:45 AM
 

alex said:

Jonathan, http://support.microsoft.com/kb/932881 Here it says specifically what it is used for and it says not to remove it.  When you apply any patch it asks for credentials so i'm not sure why if the KB article says otherwise.  What is your take on this?  I was going to remove it until I read this article so now my question is "What are the risks if any of leaving this account as is?"

February 11, 2010 6:57 PM
 

Vidhya Sagar said:

If SQL Agent service is using this account then it can have sysadmin privilege else we can drop or remove sysadmin role. In my environment SQL Services will be running under AD account so we will be removing it

February 23, 2010 12:19 PM
 

Lekss said:

I did try to remove the sysadmin for the account nt authority/system account and i could only see vss backup failing with login failed errors - it doesnt impact anything else *i think*

June 4, 2010 12:16 AM
Anonymous comments are disabled

This Blog

Syndication
©2006-2012 SQLblog.comTM
Brought to you by Adam Machanic & Peter DeBetta
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement