Application object owner accounts should be disabled when not performing installation or maintenance actions.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-5683	DG0004-SQLServer9	SV-24066r2_rule	ECLP-1	Medium

Description
Object ownership provides all database object permissions to the owned object. Access to the application object owner accounts requires special protection to prevent unauthorized access and use of the object ownership privileges. In addition to the high privileges to application objects assigned to this account, it is also an account that, by definition, is not accessed interactively except for application installation and maintenance. This reduced access to the account means that unauthorized access to the account could go undetected. To help protect the account, it should be enabled only when access is required.

Description

Object ownership provides all database object permissions to the owned object. Access to the application object owner accounts requires special protection to prevent unauthorized access and use of the object ownership privileges. In addition to the high privileges to application objects assigned to this account, it is also an account that, by definition, is not accessed interactively except for application installation and maintenance. This reduced access to the account means that unauthorized access to the account could go undetected. To help protect the account, it should be enabled only when access is required.

STIG	Date
Microsoft SQL Server 2005 Database Security Technical Implementation Guide	2015-06-16

Details

Check Text ( C-23668r2_chk )
Review list of non-default, non-DBA and non-developer object owners: From the query prompt: SELECT name AS [Database Name] FROM [master].sys.databases WHERE state = 0 Repeat for each database: From the query prompt: USE [Database Name] SELECT DISTINCT s.name AS [Schema], l.name AS [User] FROM sys.all_objects o JOIN sys.schemas s ON o.schema_id = s.schema_id JOIN sys.database_principals u ON s.principal_id = u.principal_id JOIN master.sys.sql_logins l ON u.sid = l.sid WHERE o.is_ms_shipped = 0 AND l.is_disabled = 0 If any login names are returned (not disabled) from the last part of the query, this is a Finding. Note: The 'sa' account is not exempt from this requirement and should be disabled. DBA and developer accounts authorized to own objects in the database may be permitted, but must be documented.

Check Text ( C-23668r2_chk )

Review list of non-default, non-DBA and non-developer object owners:

From the query prompt:

SELECT name AS [Database Name]
FROM [master].sys.databases
WHERE state = 0

Repeat for each database:

From the query prompt:

USE [Database Name]
SELECT DISTINCT s.name AS [Schema], l.name AS [User]
FROM sys.all_objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.database_principals u ON s.principal_id = u.principal_id
JOIN master.sys.sql_logins l ON u.sid = l.sid
WHERE o.is_ms_shipped = 0
AND l.is_disabled = 0

If any login names are returned (not disabled) from the last part of the query, this is a Finding.

Note: The 'sa' account is not exempt from this requirement and should be disabled. DBA and developer accounts authorized to own objects in the database may be permitted, but must be documented.

Fix Text (F-20174r1_fix)
Disable logins for all application object owner accounts or members of database roles that own objects: ALTER LOGIN [name] DISABLE Document application object owner accounts that are not disabled in the System Security Plan and authorize with the IAO.