SQL Server must enforce access control policies to restrict the Administer bulk operations permission to only authorized roles.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-41269	SQL2-00-005700	SV-53751r1_rule		Medium

Description
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. Organizations consider the creation of additional processes, roles, and SQL Server accounts as necessary to achieve least privilege. Organizations also apply least privilege concepts to the design, development, implementation, and operations of SQL Server and the OS. 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. SQL Server's 'Administer bulk operations' permission is a high server-level privilege that must only be granted to individual administration accounts through roles, and users who have access must require this privilege to accomplish the organizational missions and/or functions. If the 'Administer bulk operations' permission is granted to roles that are unauthorized to have this privilege, then this access must be removed.

Description

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. Organizations consider the creation of additional processes, roles, and SQL Server accounts as necessary to achieve least privilege. Organizations also apply least privilege concepts to the design, development, implementation, and operations of SQL Server and the OS. 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. SQL Server's 'Administer bulk operations' permission is a high server-level privilege that must only be granted to individual administration accounts through roles, and users who have access must require this privilege to accomplish the organizational missions and/or functions. If the 'Administer bulk operations' permission is granted to roles that are unauthorized to have this privilege, then this access must be removed.

STIG	Date
Microsoft SQL Server 2012 Database Instance Security Technical Implementation Guide	2014-01-05

Details

Check Text ( C-47837r2_chk )
Obtain the list of roles that are authorized for the SQL Server 'Administer bulk operations' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized. Obtain the list of roles with that permission by running the following query: Select * from sys.server_permissions where permission_name = 'Administer bulk operations' If any rows are returned, run the following query substituting the with those returned in the previous query. Select * from sys.server_principals where type = 'R' and principal_id = If any role has 'Grant', 'With Grant' or 'Deny' privileges to the 'Administer bulk operations' permission and the user is not authorized to have the permission, this is a finding.

Check Text ( C-47837r2_chk )

Obtain the list of roles that are authorized for the SQL Server 'Administer bulk operations' permission and what 'Grant', 'Grant With', and/or 'Deny' privilege is authorized.

Obtain the list of roles with that permission by running the following query:

Select * from sys.server_permissions where permission_name = 'Administer bulk operations'

If any rows are returned, run the following query substituting the with those returned in the previous query.

Select * from sys.server_principals where type = 'R' and principal_id =

If any role has 'Grant', 'With Grant' or 'Deny' privileges to the 'Administer bulk operations' permission and the user is not authorized to have the permission, this is a finding.

Fix Text (F-46660r1_fix)
Remove the 'Administer bulk operations' permission access from the role that is not authorized by executing the following query: REVOKE Administer bulk operations TO <'role name'>