SQL2-00-007900 - SQL Server must not grant users direct access control to the Alter Any Availability Group permission.

Information

The concept of least privilege must be applied to SQL Server processes, ensuring that the processes operate at privilege levels no higher than necessary to accomplish required organizational missions and/or functions.

Unauthorized access to sensitive data or SQL Server control may compromise the confidentiality of personnel privacy, threaten national security, compromise a variety of other sensitive operations or lead to a loss of system control. Access controls are best managed by defining requirements based on distinct job functions and assigning access based on the job function assigned to the individual user.

Privileges granted outside of SQL Server's role-based account assignments are more likely to go unmanaged and without oversight of granted access. Maintenance of privileges using roles defined for discrete job functions offers improved oversight of application user privilege assignments and helps to protect against unauthorized privilege assignment.

SQL Server's 'Alter any availability group' permission is a high server-level privilege that must only be granted to individual administration accounts through roles. This administrative privilege must not be assigned directly to administrative user accounts. If administrative user accounts have direct access to administrative roles, this access must be removed.

(The SQL Server installer gives this privilege to the system account 'NT AUTHORITY\SYSTEM', so this account is excluded from the Check. See article KB2847723 in the Microsoft knowledge base.)

Note that this does not apply to logins with names of the form '##MS...##'. These accounts are internal-use system principals provisioned by the DBMS, and required by it for specific purposes.

Solution

Remove the 'Alter Any Availability Group' permission access from the account that has direct access by using the following code. Substitute the relevant names for the text in angle brackets.

-- For each login identified in the Check:
USE master;
REVOKE ALTER ANY AVAILABILITY GROUP FROM <login name>;
GO

-- If the necessary server role does not already exist,
-- and any user identified in the Check needs this permission:
USE master;
CREATE SERVER ROLE <role name> AUTHORIZATION <appropriate principal name>;
GO
GRANT ALTER ANY AVAILABILITY GROUP TO <role name>;
GO

-- For each user identified in the Check who needs this permission:
USE master;
ALTER SERVER ROLE <role name> ADD MEMBER <login name>;
GO

Item Details

Audit Name: DISA STIG SQL Server 2012 DB Instance Security v1r20

Category: ACCESS CONTROL

Plugin: MS_SQLDB

Control ID: 695babbbb4e748cd66b42fd7dc72b111dea5179fd0c5d4f8780e2bbfbada3f6f

Detections

Analytics

SQL2-00-007900 - SQL Server must not grant users direct access control to the Alter Any Availability Group permission.

Information

Solution

See Also

Item Details