The principle of least privilege grants each security principal only the permissions needed for normal duties. Extra access for an unusual task should be temporary and revoked afterward. In SQL Server, this applies to people, applications, services, logins, users, and roles—not just DBAs. section 14.0 section 14.1
A common mistake is treating sysadmin as the easy answer to an application or vendor request. The role can do anything on an instance; granting it to non-DBA staff or a shared third-party account can turn one compromised account into access to organizational data across every affected server. section 14.1
Use a safer permission process:
- Identify the exact day-to-day operation required rather than accepting a request for broad administrative access. section 14.1
- Create a custom server or database role and grant only the required permissions. section 14.1
- Grant elevated access only for a specific exceptional task, then revoke it when the task finishes. section 14.1 section 14.8
- Challenge vendor claims that sysadmin is mandatory, especially for backup, monitoring, or security software. section 14.1
- Limit elevated accounts as one layer of defense alongside disabling unused dangerous features and auditing privileged activity. section 14.4
Replace a broad availability-group request
If an account needs to manage availability groups, a custom server role can receive permissions such as ALTER ANY AVAILABILITY GROUP, CONNECT SQL, CREATE AVAILABILITY GROUP, and VIEW SERVER STATE. This targets the stated task instead of making the account sysadmin. 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 ;
Inspect a custom server-role definition that grants specific availability-group capabilities rather than full instance control. section 14.1
Allow procedure execution without broad database rights
A database role can be created in the model database and granted EXECUTE, allowing members to run stored procedures without receiving unrelated permissions. section 14.1
Listing 14.2 Database role to allow stored procedure execution
USE model ; GO CREATE ROLE ExecSP ; GO GRANT EXECUTE TO ExecSP ;
Inspect a database-role pattern for granting stored-procedure execution. section 14.1
Least privilege also reduces the damage available to an attacker. Keeping elevated permissions rare, disabling xp_cmdshell when it is not needed, alerting on attempts to enable it, and auditing administrative activity create layers that improve the chance of detecting or stopping an attack. section 14.4 section 14.8
