Home / server / Questions / 590203
In Process
Heinzi
Asked: 2014-04-21 04:08:09 +0800 CST

Why can NT AUTHORITY\SYSTEM create SQL Server database backups?

  • 772

I have a scheduled task (in Windows Task Scheduler) that connects to SQL Server using SMO (Windows Authentication) and creates a database backup. So far, this task was running under the Administrator account and I wanted to change it to use the SYSTEM account instead.

I changed the scheduled task and, to my utter surprise, it worked out-of-the-box.

I'd like to understand why this is the case. The system is Windows Server 2012 R2, and the database is SQL Server 2012 (SP1) Express Edition. It's a standard installation with one SQL Auth user added.

In SSMS, these are the logins and their associated server roles:

  • ##MS_PolicyEventProcessingLogin## (disabled)
  • ##MS_PolicyTsqlExecutionLogin## (disabled)
  • MyServer\Administrator (public, sysadmin)
  • MySqlAuthUser (public)
  • BUILTIN\Users (public)
  • NT AUTHORITY\SYSTEM (public)
  • NT SERVICE\MSSQLSERVER (public, sysadmin)
  • NT SERVICE\SQLWriter (public, sysadmin)
  • NT SERVICE\Winmgmt (public, sysadmin)
  • sa (public, sysadmin)

The database itself has the following users and their roles:

  • MySqlAuthUser (Login MySqlAuthUser) (db_owner)
  • dbo (Login sa) (db_owner)
  • guest (disabled)
  • INFORMATION_SCHEMA (disabled)
  • sys (disabled)

Viewing the "effective permissions" of user NT AUTHORITY\SYSTEM yields the following output:

  • ALTER ANY AVAILABILITY GROUP
  • CONNECT SQL
  • CREATE AVAILABILITY GROUP
  • VIEW ANY DATABASE
  • VIEW SERVER STATE

Why does NT AUTHORITY\SYSTEM have permission to backup databases? I'm glad that it does, but I'd really like to understand why...

sql-server

1 Answers

  1. The minimum permissions to back up a database are PUBLIC at the server level and DB_BACKUPOPERATOR at a database level.

    In SQL Server 2005, Microsoft recommended against removing NT AUTHORITY\SYSTEM from the sysadmin role:

    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 NT AUTHORITY\SYSTEM account is used by Microsoft Update and by Microsoft SMS to apply service packs and hotfixes to a SQL Server 2005 installation. The NT AUTHORITY\SYSTEM account is also used by the SQL Writer Service.

    This is no longer the case in 2012. I double-checked on my own instance of 2012 Express: NT AUTHORITY\SYSTEM is not a sysadmin. However, the SQL Service VSS Writer is running as NT AUTHORITY\SYSTEM and is a sysadmin.

    I'm not able to find any links backing up that this is why NT AUTHORITY\SYSTEM is allowed to back up databases, but I believe that's the case.

    • 11