Title

Fixed Database roles should have only authorized users or groups as members. (Cat II impact)

Discussion

Fixed database roles provide a mechanism to grant groups of privileges to users. These privilege groupings are defined by the installation or upgrade of the SQL Server software at the discretion of Microsoft. Memberships in these roles granted to users should be strictly controlled and monitored. Privileges assigned to these roles should be reviewed for change after software upgrade or maintenance to ensure that the privileges continue to be appropriate to the assigned members.

Check Content

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 u.name AS [Principal], g.name AS [Role] FROM sys.database_role_members r, sys.database_principals u, sys.database_principals g WHERE r.role_principal_id = g.principal_id AND r.member_principal_id = u.principal_id AND g.is_fixed_role = 1 ORDER BY u.name, g.name The DBO membership in the db_owner fixed database role does not require explicit authorization and is Not a Finding. Verify fixed database role assignments for each Principal listed in the System Security Plan. If any Principals are not authorized, this is a Finding. Any fixed database role assignments to the Guest Principal is a finding.

Fix Text

Grant fixed database roles only to authorized accounts. Revoke unauthorized accounts from fixed database roles. From the SQL Server Management Studio GUI: To deassign roles: 1. Expand [instance name] 2. Expand Databases 3. Expand [database type] 4. Expand [database name] 5. Expand Security 6. Expand Roles 7. Expand Database Roles 8. Double-click the role to be removed from the assigned user 9. Select the user's account under Role Members 10. Click on the Remove button Document fixed database role grants in the System Security Plan and authorize with the IAO.

Additional Identifiers

Rule ID: SV-23769r2_rule

Vulnerability ID: V-15151

Group Title:

Expert Comments

CCIs tied to check.

Controls tied to check. These are derived from the CCIs shown above.