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…
Best Answer
The minimum permissions to back up a database are
PUBLIC
at the server level andDB_BACKUPOPERATOR
at a database level.In SQL Server 2005, Microsoft recommended against removing
NT AUTHORITY\SYSTEM
from the sysadmin role: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, theSQL Service VSS Writer
is running asNT 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.