SQL Server default account public must be removed from each database.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-41398	SQL2-00-023400	SV-53924r1_rule		Medium

Description
SQL Server's user-defined 'public' account(s) may be assigned privileges that could give data access to an attacker. Well-known SQL Server default accounts would likely be targeted by attackers and thus more prone to providing unauthorized access to SQL Server data. If user-defined 'public' accounts are not removed or set to having zero privileges an attacker would be more likely to gain access to SQL Server data. Any 'public' account possesses a compromise to user-defined database data that could compromise the viewing of SQL Server system data and in some cases configuration settings. All user-defined 'public' accounts must be removed to eliminate any chance of unauthorized access. The only exception to this is if there is public information that is being made available to the public.

Description

SQL Server's user-defined 'public' account(s) may be assigned privileges that could give data access to an attacker. Well-known SQL Server default accounts would likely be targeted by attackers and thus more prone to providing unauthorized access to SQL Server data. If user-defined 'public' accounts are not removed or set to having zero privileges an attacker would be more likely to gain access to SQL Server data. Any 'public' account possesses a compromise to user-defined database data that could compromise the viewing of SQL Server system data and in some cases configuration settings. All user-defined 'public' accounts must be removed to eliminate any chance of unauthorized access. The only exception to this is if there is public information that is being made available to the public.

STIG	Date
Microsoft SQL Server 2012 Database Security Technical Implementation Guide	2014-06-23

Details

Check Text ( C-47936r2_chk )
Obtain list of 'public' accounts, if any exist, from system documentation. Check SQL Server for the existence of 'public' accounts in every user-defined database instance by running the following script: EXEC sp_MSforeachdb ' IF NOT ''?'' IN (''master'', ''tempdb'', ''model'', ''msdb'') BEGIN USE ? SELECT ''?'' AS ''Database'' , su.name AS ''db Account Name'' , sp.name AS ''SQL Server Account Name'' FROM sys.sysusers su LEFT JOIN sys.server_principals sp ON su.sid = sp.sid WHERE ( su.name like ''publ%" OR sp.name like ''publ%'' ) AND NOT su.sid = CONVERT(VARBINARY(85), 0x) End' If any 'public' accounts exist and SQL Server is not setup for public information access, this is a finding. If any 'public' accounts exist that are not documented in the system documentation, this is a finding. Determine whether any of the 'public' accounts that exist have excessive privileges. Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Databases >> System Databases >> master >> Security >> Users>> right click 'public' account >> Properties >> Owned Schemas. If any 'public' account is assigned ownership of a schema, this is a finding. Navigate from "Owned Schemas" to "Membership". If any 'public' account is assigned direct membership to any "Role Members", this is a finding. Navigate from "Membership" to "Securables". If any 'public' account is assigned direct membership to any "Securables" permission other than an authorized role assignment, this is a finding. If any 'public' account is assigned user-defined role membership that is determined to have excessive privileges (more than read/select access to public data), this is a finding. Navigate from "Securables" to "Extended Properties". If any 'public' account is assigned direct "Extended Properties", this is a finding.

Check Text ( C-47936r2_chk )

Obtain list of 'public' accounts, if any exist, from system documentation.

Check SQL Server for the existence of 'public' accounts in every user-defined database instance by running the following script:

EXEC sp_MSforeachdb '
IF NOT ''?'' IN (''master'', ''tempdb'', ''model'', ''msdb'')
BEGIN
USE ?
SELECT ''?'' AS ''Database''
, su.name AS ''db Account Name''
, sp.name AS ''SQL Server Account Name''
FROM sys.sysusers su
LEFT JOIN sys.server_principals sp
ON su.sid = sp.sid
WHERE ( su.name like ''publ%"
OR sp.name like ''publ%'' )
AND NOT su.sid = CONVERT(VARBINARY(85), 0x)
End'

If any 'public' accounts exist and SQL Server is not setup for public information access, this is a finding.

If any 'public' accounts exist that are not documented in the system documentation, this is a finding.

Determine whether any of the 'public' accounts that exist have excessive privileges.

Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Databases >> System Databases >> master >> Security >> Users>> right click 'public' account >> Properties >> Owned Schemas.

If any 'public' account is assigned ownership of a schema, this is a finding.

Navigate from "Owned Schemas" to "Membership".

If any 'public' account is assigned direct membership to any "Role Members", this is a finding.

Navigate from "Membership" to "Securables".

If any 'public' account is assigned direct membership to any "Securables" permission other than an authorized role assignment, this is a finding.

If any 'public' account is assigned user-defined role membership that is determined to have excessive privileges (more than read/select access to public data), this is a finding.

Navigate from "Securables" to "Extended Properties".

If any 'public' account is assigned direct "Extended Properties", this is a finding.

Fix Text (F-46824r2_fix)
Remove 'public' account by running the following script: USE <'database name'> DROP USER <'public account name'> -- Removes user from database DROP LOGIN <'public account name'> -- Removes user from system GO Document the existence of the 'public' account in the system documentation. Remove the 'public' account as the owner of a schema by running the following script: -- Remove Owned Schemas by assigning schema to another user USE <'database name'> ALTER AUTHORIZATION ON SCHEMA::<'schema name'> TO <'account name'> GO Remove the 'public' account from direct membership of a system role by running the following script: USE <'database name'> ALTER ROLE <'role name'> DROP MEMBER <'public account name'> GO Remove the 'public' account from direct Securables access by running the following script: USE <'database name'> REVOKE <'securable name'> ON <'object_name'> TO <'public account name'> AS <'grantor name'> GO Note: <'grantor name'> is usually "[dbo]" Remove the 'public' account from direct Extended Properties. Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Databases >> System Databases >> master >> Security >> Users>> right click 'public' account >> Properties >> Extended Properties >> . Click the "Delete" button to remove the Extended Property.

Fix Text (F-46824r2_fix)

Remove 'public' account by running the following script:
USE <'database name'>
DROP USER <'public account name'> -- Removes user from database
DROP LOGIN <'public account name'> -- Removes user from system
GO

Document the existence of the 'public' account in the system documentation.

Remove the 'public' account as the owner of a schema by running the following script:
-- Remove Owned Schemas by assigning schema to another user
USE <'database name'>
ALTER AUTHORIZATION ON SCHEMA::<'schema name'> TO <'account name'>
GO

Remove the 'public' account from direct membership of a system role by running the following script:
USE <'database name'>
ALTER ROLE <'role name'> DROP MEMBER <'public account name'>
GO

Remove the 'public' account from direct Securables access by running the following script:
USE <'database name'>
REVOKE <'securable name'> ON <'object_name'> TO <'public account name'> AS <'grantor name'>
GO
Note: <'grantor name'> is usually "[dbo]"

Remove the 'public' account from direct Extended Properties.
Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Databases >> System Databases >> master >> Security >> Users>> right click 'public' account >> Properties >> Extended Properties >> .
Click the "Delete" button to remove the Extended Property.