Part of the DBA-Tools Project.
What’s Actually Happening at the Login Level, Not Just Who’s Allowed
Permissions and Role Membership covers who can do what. These three scripts cover something different: what’s actually happening at the login layer right now. Get-FailedLoginSummary surfaces brute-force patterns and locked accounts from the error log. Get-LoginLastActivity shows current session status and connection details for every login. Get-WeakLoginSettings flags SQL logins with password policy off, expiration off, or sa enabled, the configuration gaps that make a login easier to compromise in the first place.
Why Login Security Matters
- A pattern of failed logins from one client host is a real, actionable brute-force signal, not just noise in the error log
- SQL Server does not track “last login time” natively without a configured Server Audit, so knowing what’s actually available (current sessions, not history) prevents a false assumption during an investigation
- Password policy and expiration checks being off is a silent, common gap, SQL logins created without
CHECK_POLICYinherit none of Windows’ password complexity or lockout protection saenabled is one of the highest-value single findings in any security review, it’s the most-targeted account name in the world for exactly this reason
When to Run These Scripts
- Get-FailedLoginSummary — when investigating suspicious activity, or as a routine health check (it’s part of the standard health-check suite)
- Get-LoginLastActivity — when reviewing which logins are actually in active use before disabling or removing anything
- Get-WeakLoginSettings — during any security review or when inheriting a server, it’s also part of the standard health-check suite
- Together, as a login-level security pass alongside Permissions and Role Membership
The Scripts
1. Get-FailedLoginSummary — Brute-Force Patterns and Lockout State
/*
Script Name : Get-FailedLoginSummary
Category : security
Purpose : Aggregated failed login analysis from the SQL Server error log and current
lockout state per SQL login. Surfaces brute-force patterns and locked accounts.
Complements Get-WeakLoginSettings (which checks policy configuration), this
checks what is actually happening.
Note: SQL Server 2025 does not write 18456 events to RING_BUFFER_SECURITY_ERROR;
xp_readerrorlog is the reliable cross-version source for login failures.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-login-security-audit/)
Requires : VIEW SERVER STATE, sysadmin (for LOGINPROPERTY on other logins), EXECUTE on xp_readerrorlog
HealthCheck : Yes
*/
-- Blog: https://sqldba.blog/dba-scripts-get-login-security-audit/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
-- INSERT...EXEC cannot be used inside a CTE so materialise to a temp table first
CREATE TABLE #failed_logins (
log_date DATETIME,
process_info NVARCHAR(100),
log_text NVARCHAR(MAX)
);
-- Filter to current error log (log# 0), type 1 (SQL Server log), login failure messages only
INSERT INTO #failed_logins
EXEC xp_readerrorlog 0, 1, N'Login failed';
WITH parsed AS (
SELECT
-- Login name sits between the first pair of single quotes
SUBSTRING(
log_text,
CHARINDEX('''', log_text) + 1,
CHARINDEX('''', log_text, CHARINDEX('''', log_text) + 1) - CHARINDEX('''', log_text) - 1
) AS login_name,
-- Client IP/host is in the trailing [CLIENT: ...] tag
CASE
WHEN CHARINDEX('[CLIENT: ', log_text) > 0
THEN SUBSTRING(
log_text,
CHARINDEX('[CLIENT: ', log_text) + 9,
CHARINDEX(']', log_text, CHARINDEX('[CLIENT: ', log_text))
- CHARINDEX('[CLIENT: ', log_text) - 9
)
ELSE NULL
END AS client_host,
-- Map reason text to the canonical error code
CASE
WHEN log_text LIKE '%untrusted domain%' THEN 18452
WHEN log_text LIKE '%only administrators%' THEN 18451
WHEN log_text LIKE '%account is disabled%' THEN 18470
WHEN log_text LIKE '%password must be changed%' THEN 18488
WHEN log_text LIKE '%password did not match%' THEN 18456
WHEN log_text LIKE '%could not find a login%' THEN 18456
ELSE 18456
END AS error_code,
log_date
FROM #failed_logins
),
aggregated AS (
SELECT
login_name,
client_host,
error_code,
COUNT(*) AS failure_count,
MIN(log_date) AS first_failure,
MAX(log_date) AS last_failure
FROM parsed
GROUP BY login_name, client_host, error_code
)
SELECT
agg.login_name,
agg.client_host,
agg.error_code,
CASE agg.error_code
WHEN 18456 THEN 'Login failed (bad password or login does not exist)'
WHEN 18452 THEN 'Login from untrusted domain or cannot use Windows auth'
WHEN 18451 THEN 'Login failed — only admin connections are allowed'
WHEN 18470 THEN 'Account is disabled'
WHEN 18488 THEN 'Password must be changed'
WHEN 4818 THEN 'Password does not meet complexity requirements'
ELSE 'Error ' + CAST(agg.error_code AS VARCHAR(10))
END AS error_description,
agg.failure_count,
agg.first_failure AS first_failure_approx,
agg.last_failure AS last_failure_approx,
CASE
WHEN sl.name IS NOT NULL
THEN CAST(LOGINPROPERTY(sl.name, 'IsLocked') AS BIT)
ELSE NULL
END AS is_currently_locked,
CASE
WHEN sl.name IS NOT NULL
THEN CAST(LOGINPROPERTY(sl.name, 'BadPasswordCount') AS INT)
ELSE NULL
END AS bad_password_count,
CASE
WHEN agg.failure_count >= 50
THEN 'CRITICAL — ' + CAST(agg.failure_count AS VARCHAR) +
' failures in error log; likely brute-force or application misconfiguration'
WHEN agg.failure_count >= 10
THEN 'WARN — repeated failures for login [' + ISNULL(agg.login_name, '(unknown)') + ']'
ELSE 'INFO'
END AS status
FROM aggregated AS agg
LEFT JOIN sys.sql_logins AS sl ON sl.name = agg.login_name
ORDER BY agg.failure_count DESC;
DROP TABLE #failed_logins;
2. Get-LoginLastActivity — Current Session Status and Connection Details
/*
Script Name : Get-LoginLastActivity
Category : monitoring
Purpose : All SQL and Windows logins with current session status, connection details,
and disabled/locked state.
Note: SQL Server does not record "last login time" natively without a SQL
Server Audit configured. This script shows what is available: current active
sessions and login metadata. For historical last-login tracking, enable a
Server Audit with LOGIN action group.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-login-security-audit/)
Requires : VIEW SERVER STATE, VIEW ANY DEFINITION
*/
-- Blog: https://sqldba.blog/dba-scripts-get-login-security-audit/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT
sp.name AS login_name,
sp.type_desc AS login_type,
sp.is_disabled,
sp.create_date,
sp.modify_date,
/* Current session info — NULL when not connected right now */
COUNT(s.session_id) AS active_sessions,
MIN(s.login_time) AS earliest_current_session,
MAX(s.login_time) AS latest_current_session,
/* Connection detail (most recent active session) */
MAX(c.client_net_address) AS last_client_ip,
MAX(s.host_name) AS last_host_name,
MAX(s.program_name) AS last_program_name,
MAX(c.auth_scheme) AS auth_scheme,
/* Permission summary */
IS_SRVROLEMEMBER('sysadmin', sp.name) AS is_sysadmin,
IS_SRVROLEMEMBER('securityadmin', sp.name) AS is_securityadmin
FROM sys.server_principals sp
LEFT JOIN sys.dm_exec_sessions s ON s.login_name = sp.name AND s.is_user_process = 1
LEFT JOIN sys.dm_exec_connections c ON c.session_id = s.session_id
WHERE sp.type IN ('S', 'U', 'G') /* SQL login, Windows user, Windows group */
AND sp.name NOT LIKE '##%' /* exclude internal system logins */
GROUP BY sp.name, sp.type_desc, sp.is_disabled, sp.create_date, sp.modify_date
ORDER BY active_sessions DESC, sp.is_disabled, sp.name;
3. Get-WeakLoginSettings — Password Policy, Expiration, and SA Status
/*
Script Name : Get-WeakLoginSettings
Category : security-and-permissions
Purpose : Identify SQL logins with weak security settings: policy off, expiration off, or sa enabled.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-login-security-audit/)
Requires : VIEW ANY DATABASE, sysadmin to see LOGINPROPERTY details
HealthCheck : Yes
*/
-- Blog: https://sqldba.blog/dba-scripts-get-login-security-audit/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT
sl.name AS login_name,
sl.is_disabled,
sl.is_policy_checked,
sl.is_expiration_checked,
CAST(LOGINPROPERTY(sl.name, 'PasswordLastSetTime') AS DATETIME) AS password_last_set,
CAST(LOGINPROPERTY(sl.name, 'IsLocked') AS BIT) AS is_locked,
CAST(LOGINPROPERTY(sl.name, 'IsMustChange') AS BIT) AS must_change_password,
sl.default_database_name,
sl.create_date,
sl.modify_date,
CASE
WHEN sl.name = 'sa' AND sl.is_disabled = 0 THEN 'SA_ENABLED'
WHEN sl.is_policy_checked = 0 THEN 'PASSWORD_POLICY_OFF'
WHEN sl.is_expiration_checked = 0 THEN 'EXPIRATION_OFF'
ELSE 'OK'
END AS risk_flag
FROM sys.sql_logins AS sl
WHERE sl.name NOT LIKE '##%'
ORDER BY
CASE
WHEN sl.name = 'sa' AND sl.is_disabled = 0 THEN 0
WHEN sl.is_policy_checked = 0 THEN 1
WHEN sl.is_expiration_checked = 0 THEN 2
ELSE 3
END,
sl.name;
How To Run From The Repo
Clone DBA Tools, initialize and run whichever script answers your question:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Failed login patterns and lockout state:
.\run.ps1 Get-FailedLoginSummary
# Current session status and connection details per login:
.\run.ps1 Get-LoginLastActivity
# Weak password policy, expiration, and sa status:
.\run.ps1 Get-WeakLoginSettings
# Any of these against a remote sql server:
.\run.ps1 Get-WeakLoginSettings -ServerInstance SQLSERVER01
These scripts live in the repo at:
sql/security/access/Get-FailedLoginSummary.sqlsql/security/access/Get-LoginLastActivity.sqlsql/security/access/Get-WeakLoginSettings.sql
Example Output
Real output from this lab instance, not staged.
Get-FailedLoginSummary (1 row, a genuine real failed login attempt captured):
| login_name | client_host | error_code | error_description | failure_count |
|---|---|---|---|---|
| demo_bad_login | 192.168.1.109 | 18456 | Login failed (bad password or login does not exist) | 1 |
Get-LoginLastActivity (17 rows, condensed here):
| login_name | login_type | is_disabled | active_sessions |
|---|---|---|---|
| pete | SQL_LOGIN | False | 0 |
| sa | SQL_LOGIN | False | 0 |
| DemoDisabledAdmin | SQL_LOGIN | True | 0 |
Get-WeakLoginSettings (10 rows, condensed here, a genuinely useful real finding: most logins on this box have password policy off):
| login_name | is_policy_checked | is_expiration_checked | risk_flag |
|---|---|---|---|
| sa | True | False | OK |
| pete | True | False | OK |
| AppSupport_User | False | False | PASSWORD_POLICY_OFF |
| DBA_Junior | False | False | PASSWORD_POLICY_OFF |
| DemoAdminUser | False | False | PASSWORD_POLICY_OFF |
Understanding the Results
- failure_count >= 50 — flagged CRITICAL, likely brute-force or a misconfigured application retrying with the wrong credentials, not casual mistyping
- is_currently_locked = 1 — the account is locked out right now from repeated bad password attempts; investigate before unlocking, don’t assume it’s benign
- active_sessions = 0 for a login you expected to be in use — worth checking, either the login isn’t actually needed anymore or something changed in how it connects
- risk_flag = PASSWORD_POLICY_OFF — the SQL login was created without
CHECK_POLICY = ON, meaning it inherits none of Windows’ complexity, history, or lockout protections; this is a genuinely common gap, several logins on this lab box show it - risk_flag = SA_ENABLED — the single highest-priority finding this script can return;
sais the most-targeted login name in the world, disable it if an equivalent named sysadmin account exists
Best Practices
- Treat any
CRITICALresult from Get-FailedLoginSummary as worth same-day investigation, especially a pattern from one specific client host - Fix
PASSWORD_POLICY_OFFandEXPIRATION_OFFfindings directly withALTER LOGIN [name] WITH CHECK_POLICY = ON, CHECK_EXPIRATION = ON, rather than just noting them - Disable
sawherever a named, audited sysadmin account already exists to do the same job - Use Get-LoginLastActivity before disabling or dropping any login, current session absence doesn’t confirm the login is unused, only that nothing’s connected right now
Related Scripts
You may also find these scripts useful:
Frequently Asked Questions
Does this tell me exactly when a login last connected, historically?
Not by default. SQL Server doesn’t track historical last-login time without a configured Server Audit with the LOGIN action group enabled. Get-LoginLastActivity shows what’s available without one: current active sessions and login metadata, genuinely useful, but not a substitute for audit-based history if you need it.
Why does SQL Server 2025 need xp_readerrorlog instead of the ring buffer?
RING_BUFFER_SECURITY_ERROR no longer captures 18456 failed-login events on SQL Server 2025 in this repo’s testing. xp_readerrorlog reading the actual error log text is the reliable, cross-version source this script relies on instead.
Summary
Login-level security is a different question from permissions: it’s about what’s actually happening right now (failed attempts, active sessions) and what’s configured to make compromise harder or easier (password policy, sa status). These three scripts turn that into a direct, actionable checklist rather than something inferred indirectly from other checks.
Run all three as part of any security review, and treat SA_ENABLED and a CRITICAL failed-login pattern as the two findings that deserve immediate attention over everything else here.
Leave a Reply