大网数据广告活动
服务器运维问答

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

· 52CE 我爱测

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 chang…

问题原文

原帖链接 · Heinzi · CC BY-SA 3.0

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

社区高分解答

原帖链接 · Katherine Villyard · CC BY-SA 3.0

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.

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2014-04-20)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 3.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。