The sysadmin fixed server role has full access to a SQL Server instance: a member can do anything. That power makes it unsuitable as a routine shortcut for application teams, vendors, or service accounts that need only a narrow operational capability. section 14.1
Mistake #94: treating sysadmin as the answer to an unclear permission request. Requests to create databases in nonproduction, or claims that backup, monitoring, or security software needs sysadmin everywhere, often reflect unexamined requirements rather than a necessary design. Granting one shared account sysadmin across an enterprise creates a severe exposure: compromising it can expose all organizational data. section 14.1
Use least privilege instead:
- Grant only the permissions needed for the routine task, then grant and revoke extra privileges only for a specific temporary task. section 14.1
- Define a custom server role for a bounded job rather than adding its account to sysadmin. section 14.1
- Audit indirect administrative paths as well as sysadmin membership, including CONTROL SERVER and IMPERSONATE permissions. section 14.4
A narrowly scoped failover role
An Availability Groups failover account can receive a custom server role with ALTER ANY AVAILABILITY GROUP, CONNECT SQL, CREATE AVAILABILITY GROUP, and VIEW SERVER STATE. This grants named capabilities instead of unrestricted instance administration. section 14.1
Do not assume sysadmin membership is the only route to dangerous actions. xp_cmdshell can also be reached through CONTROL SERVER, EXECUTE permission with an xp_cmdshell proxy, or impersonation of a sufficiently privileged user. Those unusual routes may receive less scrutiny than the sysadmin member list. section 14.4
Sysadmin is powerful but not universally able to read protected data: Always Encrypted can prevent even sysadmin members from decrypting and viewing it. Encryption choices therefore complement, rather than replace, careful administrative permission design. section 14.6
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 ;
Inspect a concrete custom server role that scopes permissions to Availability Groups failover operations. section 14.1
