STIGQter: STIG Summary: MS SQL Server 2016 Database Security Technical Implementation Guide Version: 2 Release: 1 Benchmark Date: 23 Oct 2020:

SQL Server must enforce access restrictions associated with changes to the configuration of the database(s).

DISA Rule

SV-213924r508025_rule

Vulnerability Number

V-213924

Group Title

SRG-APP-000380-DB-000360

Rule Version

SQL6-D0-003100

Severity

CAT II

CCI(s)

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

Weight

10

Fix Recommendation

Remove unauthorized users from roles:

ALTER ROLE DROP MEMBER user;

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

Set the owner of the database to an authorized login:

ALTER AUTHORIZATION ON database::DatabaseName TO login;

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

Check Contents

Execute the following query to obtain a listing of user databases whose owner is a member of a fixed server role:

SELECT
D.name AS database_name, SUSER_SNAME(D.owner_sid) AS owner_name,
FRM.is_fixed_role_member
FROM sys.databases D
OUTER APPLY (
SELECT MAX(fixed_role_member) AS is_fixed_role_member
FROM (
SELECT IS_SRVROLEMEMBER(R.name, SUSER_SNAME(D.owner_sid)) AS fixed_role_member
FROM sys.server_principals R
WHERE is_fixed_role = 1
) A
) FRM
WHERE D.database_id > 4
AND (FRM.is_fixed_role_member = 1
OR FRM.is_fixed_role_member IS NULL)
ORDER BY database_name

If no databases are returned, this is not a finding.

For each database/login returned, review the Server Role memberships

1. In SQL Server Management Studio, Expand “Logins”
2. Double-click the name of the Login
3. Click the “Server Roles” tab

If any server roles are selected, but not documented and authorized, this is a finding.

Vulnerability Number

V-213924

Documentable

False

Rule Version

SQL6-D0-003100

Severity Override Guidance

Execute the following query to obtain a listing of user databases whose owner is a member of a fixed server role:

SELECT
D.name AS database_name, SUSER_SNAME(D.owner_sid) AS owner_name,
FRM.is_fixed_role_member
FROM sys.databases D
OUTER APPLY (
SELECT MAX(fixed_role_member) AS is_fixed_role_member
FROM (
SELECT IS_SRVROLEMEMBER(R.name, SUSER_SNAME(D.owner_sid)) AS fixed_role_member
FROM sys.server_principals R
WHERE is_fixed_role = 1
) A
) FRM
WHERE D.database_id > 4
AND (FRM.is_fixed_role_member = 1
OR FRM.is_fixed_role_member IS NULL)
ORDER BY database_name

If no databases are returned, this is not a finding.

For each database/login returned, review the Server Role memberships

1. In SQL Server Management Studio, Expand “Logins”
2. Double-click the name of the Login
3. Click the “Server Roles” tab

If any server roles are selected, but not documented and authorized, this is a finding.

Check Content Reference

M

Target Key

3992

Comments