DBA Scripts: Get Login Security Audit

---

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_POLICY inherit none of Windows’ password complexity or lockout protection
• sa enabled 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
*/
-- 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
*/
-- 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
*/
-- 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.sql
• sql/security/access/Get-LoginLastActivity.sql
• sql/security/access/Get-WeakLoginSettings.sql

---

Example Output

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; sa is the most-targeted login name in the world, disable it if an equivalent named sysadmin account exists

---

Best Practices

• Treat any CRITICAL result from Get-FailedLoginSummary as worth same-day investigation, especially a pattern from one specific client host
• Fix PASSWORD_POLICY_OFF and EXPIRATION_OFF findings directly with ALTER LOGIN [name] WITH CHECK_POLICY = ON, CHECK_EXPIRATION = ON, rather than just noting them
• Disable sa wherever 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

Microsoft Learn: sys.sql_logins · sys.dm_exec_sessions · sys.dm_exec_connections

---

Related Scripts

You may also find these scripts useful:

• Security (hub)
• Permissions and Role Membership
• Locked Out of SQL Server: Regain Sysadmin When Nobody Has It, the recovery when the audit shows no usable sysadmin left
• Sysadmin Members
• Orphaned Users
• User Permissions Audit
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Frequently Asked Questions

---

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.