Mistake: accepting a vendor’s demand for sysadmin access. A backup tool may need the MS_DatabaseManager fixed server role plus db_backupoperator in databases it backs up—not unrestricted access to every instance. section 14.1
Introduced in SQL Server 2022, MS_DatabaseManager grants permission to create and delete databases. Granting less privilege limits the damage from a compromised account and reduces untracked changes that can harm performance or stability. section 14.1
A safer backup configuration
For a backup account, evaluate whether MS_DatabaseManager and db_backupoperator meet the requirement before considering sysadmin. On earlier SQL Server versions, db_creator can be used instead. section 14.1
Apply the least-privilege checklist:
- Reject blanket sysadmin requests when a fixed server or database role fits. section 14.1
- Create a custom role when no built-in role supplies the required permissions. section 14.1
Listing 14.1 Creating a custom server role
CREATE SERVER ROLE AvailabilityGroupsFailover ; GO ALTER SERVER ROLE AvailabilityGroupsFailover ADD MEMBER [NT AUTHORITY\SYSTEM] ; GRANT ALTER ANY AVAILABILITY GROUP TO AvailabilityGroupsFailover ; GRANT CONNECT SQL TO AvailabilityGroupsFailover ; GRANT CREATE AVAILABILITY GROUP TO AvailabilityGroupsFailover ; GRANT VIEW SERVER STATE TO AvailabilityGroupsFailover ;
Use a custom server role to grant narrowly defined permissions when built-in roles do not fit. section 14.1
