STIGQter STIGQter: STIG Summary: MS SQL Server 2014 Database Security Technical Implementation Guide Version: 1 Release: 6 Benchmark Date: 26 Jan 2018:

In a database owned by [sa], or by any other login having administrative privileges at the instance level, the database property TRUSTWORTHY must be OFF.

DISA Rule

SV-81865r1_rule

Vulnerability Number

V-67375

Group Title

SRG-APP-000133-DB-000200

Rule Version

SQL4-00-015610

Severity

CAT II

CCI(s)

Weight

10

Fix Recommendation

Set the TRUSTWORTHY property OFF; or remove the database owner from the fixed server role(s); or change the database owner.

To set the TRUSTWORTHY property OFF:
USE [master];
GO
ALTER DATABASE <name> SET TRUSTWORTHY OFF;
GO
Verify that this produced the intended result by re-running the query specified in the Check.

To determine the path or paths by which the database owner is assigned the fixed server role or roles, run this query:

USE <database name>;
GO
WITH C AS
(
SELECT
P.name AS [Parent Server Role],
CAST('Fixed' AS varchar(8))
AS [Server Role Type],
M.name AS [Member],
M.type_desc AS [Member Type],
P.name AS [Root],
1 AS [Level]
FROM
[sys].[server_role_members] X
INNER JOIN [sys].[server_principals] P ON P.principal_id = X.role_principal_id
INNER JOIN [sys].[server_principals] M ON M.principal_id = X.member_principal_id
WHERE
P.is_fixed_role = 1
UNION ALL
SELECT
P.name AS [Parent Server Role],
CASE WHEN M.is_fixed_role = 1 THEN CAST('Fixed' AS varchar(8)) ELSE CAST('Custom' AS varchar(8)) END
AS [Server Role Type],
M.name AS [Member],
M.type_desc AS [Member Type],
C.[Root] AS [Root],
C.[Level] + 1 AS [Level]
FROM
[sys].[server_role_members] X
INNER JOIN [sys].[server_principals] P ON P.principal_id = X.role_principal_id
INNER JOIN [sys].[server_principals] M ON M.principal_id = X.member_principal_id
INNER JOIN C ON P.name = C.Member
)
,
B AS
(
SELECT
C.[Member] AS [Leaf],
C.[Root],
C.[Parent Server Role],
C.[Server Role Type],
C.[Member],
C.[Member Type],
C.[Level]
FROM C
WHERE
C.[Member Type] NOT LIKE '%ROLE%'
UNION ALL
SELECT
B.[Leaf],
C.[Root],
C.[Parent Server Role],
C.[Server Role Type],
C.[Member],
C.[Member Type],
C.[Level]
FROM C
INNER JOIN B
ON C.[Member] = B.[Parent Server Role]
AND C.[Level] = B.[Level] - 1
AND C.[Root] = B.[Root]
)
SELECT
DB_NAME() AS [Database],
B.[Leaf] AS [Owner Login],
B.[Root] AS[Top-Level Server Role],
B.[Parent Server Role],
B.[Server Role Type],
B.[Member],
B.[Member Type],
B.[Level]
FROM B
WHERE B.[Leaf] = (SELECT SUSER_SNAME(D.owner_sid) FROM sys.databases D WHERE D.Name = DB_NAME())
ORDER BY B.[Root], B.[Level], B.[Parent Server Role], B.[Member]
;
GO

To remove the database owner from a fixed server role or a custom server role:
USE [master];
GO
ALTER SERVER ROLE <fixed/custom server role name>
DROP MEMBER <database owner name>;
GO
Verify that this produced the intended result by re-running the Check query.

To change the database owner:
USE [master];
GO
ALTER AUTHORIZATION ON DATABASE::<DB name> TO <new owner name>;
GO
Verify that this produced the intended result by re-running the Check query.

Check Contents

Run the SQL statements:
USE <database name>;
GO
WITH FixedServerRoles(RoleName) AS
(
SELECT 'sysadmin'
UNION SELECT 'securityadmin'
UNION SELECT 'serveradmin'
UNION SELECT 'setupadmin'
UNION SELECT 'processadmin'
UNION SELECT 'diskadmin'
UNION SELECT 'dbcreator'
UNION SELECT 'bulkadmin'
)
SELECT
DB_NAME() AS [Database],
SUSER_SNAME(D.owner_sid) AS [Database Owner],
F.RoleName AS [Fixed Server Role],
CASE WHEN D.is_trustworthy_on = 1 THEN 'ON' ELSE 'off' END
AS [Trustworthy]
FROM
FixedServerRoles F
INNER JOIN sys.databases D ON D.Name = DB_NAME()
WHERE
IS_SRVROLEMEMBER(F.RoleName, SUSER_SNAME(D.owner_sid)) = 1
AND DB_NAME() <> 'msdb'
AND D.is_trustworthy_on = 1;
GO

If the query returns any rows, this is a finding.

Vulnerability Number

V-67375

Documentable

False

Rule Version

SQL4-00-015610

Severity Override Guidance

Run the SQL statements:
USE <database name>;
GO
WITH FixedServerRoles(RoleName) AS
(
SELECT 'sysadmin'
UNION SELECT 'securityadmin'
UNION SELECT 'serveradmin'
UNION SELECT 'setupadmin'
UNION SELECT 'processadmin'
UNION SELECT 'diskadmin'
UNION SELECT 'dbcreator'
UNION SELECT 'bulkadmin'
)
SELECT
DB_NAME() AS [Database],
SUSER_SNAME(D.owner_sid) AS [Database Owner],
F.RoleName AS [Fixed Server Role],
CASE WHEN D.is_trustworthy_on = 1 THEN 'ON' ELSE 'off' END
AS [Trustworthy]
FROM
FixedServerRoles F
INNER JOIN sys.databases D ON D.Name = DB_NAME()
WHERE
IS_SRVROLEMEMBER(F.RoleName, SUSER_SNAME(D.owner_sid)) = 1
AND DB_NAME() <> 'msdb'
AND D.is_trustworthy_on = 1;
GO

If the query returns any rows, this is a finding.

Check Content Reference

M

Target Key

2637

Comments