STIGQter: STIG Summary: MS SQL Server 2016 Instance Security Technical Implementation Guide Version: 2 Release: 3 Benchmark Date: 23 Apr 2021:

SQL Server must enforce access restrictions associated with changes to the configuration of the instance.

DISA Rule

SV-213987r617437_rule

Vulnerability Number

V-213987

Group Title

SRG-APP-000380-DB-000360

Rule Version

SQL6-D0-011400

Severity

CAT II

CCI(s)

• CCI-001813 - The information system enforces access restrictions.

Weight

10

Fix Recommendation

Revoke unauthorized permissions from principals.

https://msdn.microsoft.com/en-us/library/ms186308.aspx

Remove unauthorized logins from roles.

ALTER SERVER ROLE DROP MEMBER login;

https://technet.microsoft.com/en-us/library/ee677634.aspx

Check Contents

Obtain a list of logins who have privileged permissions and role memberships in SQL.

Execute the following query to obtain a list of logins and roles and their respective permissions assignment:

SELECT p.name AS Principal,
p.type_desc AS Type,
sp.permission_name AS Permission,
sp.state_desc AS State
FROM sys.server_principals p
INNER JOIN sys.server_permissions sp ON p.principal_id = sp.grantee_principal_id
WHERE sp.permission_name = 'CONTROL SERVER'
OR sp.state = 'W'

Execute the following query to obtain a list of logins and their role memberships.

SELECT m.name AS Member,
m.type_desc AS Type,
r.name AS Role
FROM sys.server_principals m
INNER JOIN sys.server_role_members rm ON m.principal_id = rm.member_principal_id
INNER JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id
WHERE r.name IN ('sysadmin','securityadmin','serveradmin')

Check the server documentation to verify the logins and roles returned are authorized. If the logins and/or roles are not documented and authorized, this is a finding.

Vulnerability Number

V-213987

Documentable

False

Rule Version

SQL6-D0-011400

Severity Override Guidance

Obtain a list of logins who have privileged permissions and role memberships in SQL.

Execute the following query to obtain a list of logins and roles and their respective permissions assignment:

SELECT p.name AS Principal,
p.type_desc AS Type,
sp.permission_name AS Permission,
sp.state_desc AS State
FROM sys.server_principals p
INNER JOIN sys.server_permissions sp ON p.principal_id = sp.grantee_principal_id
WHERE sp.permission_name = 'CONTROL SERVER'
OR sp.state = 'W'

Execute the following query to obtain a list of logins and their role memberships.

SELECT m.name AS Member,
m.type_desc AS Type,
r.name AS Role
FROM sys.server_principals m
INNER JOIN sys.server_role_members rm ON m.principal_id = rm.member_principal_id
INNER JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id
WHERE r.name IN ('sysadmin','securityadmin','serveradmin')

Check the server documentation to verify the logins and roles returned are authorized. If the logins and/or roles are not documented and authorized, this is a finding.

Check Content Reference

M

Target Key

3993

Comments