Windows LOCAL groups

Job details

Name:	Windows LOCAL groups
Platform:	Sqlserver
Category:	Security
Premium package:	Security and compliance package
Description:	Checks that Windows LOCAL groups are not SQL Logins.
Long description:	Checks that Windows LOCAL groups are not SQL Logins. These groups should not be used for any level of access into a SQL Server Database Engine instance.
Version:	1.2
Default schedule:	33 1 1 *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name	Default value	Description
return status	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when Windows LOCAL groups is a SQL Login.
disable login	NO	If set to “YES“ the alert will disable the login by running “ALTER LOGIN [login_name] DISABLE“.
history threshold	365	The maximum number of days to keep statistics for in the historic tables.

Job Summary

Purpose: The purpose of this job is to monitor and ensure that Windows LOCAL groups are not configured as SQL Logins in a SQL Server Database Engine instance.
Why: This job is crucial to maintain security protocols by ensuring that local Windows groups, which should not have database access, are not mistakenly given SQL login rights. This prevention helps mitigate unauthorized access and potential security breaches.
Manual checking:
To manually check this information, you can issue the following SQL commands:

SELECT pr.[name], pe.[permission_name] FROM sys.server_principals pr JOIN sys.server_permissions pe ON pr.[principal_id] = pe.[grantee_principal_id] WHERE pr.[type_desc] = 'WINDOWS_GROUP' AND pr.[name] LIKE CAST(SERVERPROPERTY('MachineName') AS nvarchar) + '%'

Implementation Details

The job involves creating tables to store current statuses and historical data regarding Windows LOCAL groups acting as SQL logins.
Procedures are implemented to insert, update, and clean these tables based on the current status and history threshold.
SQL Cursors are used to iterate over server principals to identify any LOCAL groups configured as SQL Logins.
An option exists within the parameters to disable the SQL login if it is incorrectly set up as a LOCAL group.
Historical data management is incorporated through parameters which dictate the longevity of the recorded data.

Dependencies and Code Structure

Main Dependencies:
“dbw_windows_local_groups_proc” (Type 2 – Main procedure)

Reporting and Visualization

Report Template v2:
Title: “Windows LOCAL groups”

Upgrade and Compatibility

The job contains upgrade scripts ensuring compatibility and correct functionality with updates.
Designed to work with Microsoft SQL Server versions greater than 2005 and includes compatibility for Azure SQL Managed Instances.
A compatibility query ensures that the job is deployed only on suitable SQL Server instances to prevent execution errors.

Express Installation: False
Force Installation: True

Compliance and Security Framework

This job aligns with access control compliance by ensuring that only appropriately authorized groups and users have SQL login capabilities, safeguarding against potential unauthorized access that violates corporate security policies.