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:
- 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...