SQL Server must specifically prohibit or restrict the use of unauthorized functions and services in each instance.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-40934	SQL2-00-017300	SV-53288r1_rule		Medium

Description
SQL Server is capable of providing a wide variety of functions and services. Some of the functions and services, provided by default, may not be necessary to support essential organizational operations (e.g., key missions, functions). Additionally, it is sometimes convenient to provide multiple services from a single component of an information system (e.g., email and web services), but doing so increases risk over limiting the services provided by any one component.

Description

SQL Server is capable of providing a wide variety of functions and services. Some of the functions and services, provided by default, may not be necessary to support essential organizational operations (e.g., key missions, functions). Additionally, it is sometimes convenient to provide multiple services from a single component of an information system (e.g., email and web services), but doing so increases risk over limiting the services provided by any one component.

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

Details

Check Text ( C-47589r2_chk )
Review the list of user-defined Stored Procedures and Functions by running the following SQL query: EXEC sp_MSforeachdb ' DECLARE @nCount integer SELECT @nCount = Count() FROM ?.sys.objects WHERE type in (''FN'', ''P'') AND is_ms_shipped <> 1 IF @nCount > 0 SELECT ''?'' AS ''Table Name'', FROM ?.sys.objects WHERE type in (''FN'', ''P'') AND is_ms_shipped <> 1 ' If any user-defined Stored Procedures and Functions are unauthorized and therefore should be prohibited or restricted and are not, this is a finding.

Check Text ( C-47589r2_chk )

Review the list of user-defined Stored Procedures and Functions by running the following SQL query:
EXEC sp_MSforeachdb '
DECLARE @nCount integer

SELECT @nCount = Count(*)
FROM ?.sys.objects
WHERE type in (''FN'', ''P'')
AND is_ms_shipped <> 1

IF @nCount > 0
SELECT ''?'' AS ''Table Name'', *
FROM ?.sys.objects
WHERE type in (''FN'', ''P'')
AND is_ms_shipped <> 1 '

If any user-defined Stored Procedures and Functions are unauthorized and therefore should be prohibited or restricted and are not, this is a finding.

Fix Text (F-46216r2_fix)
To remove a function from SQL Server, run the following SQL Script: DROP FUNCTION <'function name'> To remove a Stored Procedure from SQL Server, run the following SQL Script: DROP PROCEDURE <'stored procedure name'> If the user-defined Stored Procedures and Functions need to remain available, but access needs to be more restricted, then the user-defined Stored Procedures and Functions should be moved to a separate schema or database that has more restrictive access.

Fix Text (F-46216r2_fix)

To remove a function from SQL Server, run the following SQL Script:
DROP FUNCTION <'function name'>

To remove a Stored Procedure from SQL Server, run the following SQL Script:
DROP PROCEDURE <'stored procedure name'>

If the user-defined Stored Procedures and Functions need to remain available, but access needs to be more restricted, then the user-defined Stored Procedures and Functions should be moved to a separate schema or database that has more restrictive access.