patternsqlMinor

SQL unused logins

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

sqlunusedlogins

Problem

is there a way to find out logins that have not been logged into the SQL Server or accessed the databases in the past 90 days ?

Solution

You can capture this information with an Extended Events session:

CREATE EVENT SESSION [Audit_Logon] ON SERVER 
ADD EVENT sqlserver.LOGIN (
    SET  collect_database_name = (1)    
        ,collect_options_text = (0) 
    ACTION(
         sqlserver.client_app_name
        ,sqlserver.client_hostname
        ,sqlserver.server_principal_name
    )
)
WITH (
     MAX_MEMORY = 4096 KB
    ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
    ,MAX_DISPATCH_LATENCY = 30 SECONDS
    ,MAX_EVENT_SIZE = 0 KB
    ,MEMORY_PARTITION_MODE = NONE
    ,TRACK_CAUSALITY = OFF
    ,STARTUP_STATE = ON
)
GO

Then you can stream the events captured in the session and process them with a powershell script.

First of all, you will need a couple of tables to store the results. In this case you can use a staging table to store the events temporarily and a target table to store the events in a summarized form. You are not interested in the individual events, you only need to capture when the last successful logon was recorded. In case this makes sense to you, you can group events by some meaningful attributes:

LoginName

HostName

ApplicationName

DatabaseName

Create these tables in master:

CREATE TABLE [dbo].[AuditLogin](
    [LoggingID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    [LoginName] [sysname] NOT NULL,
    [HostName] [varchar](100) NULL,
    [NTUserName] [varchar](100) NULL,
    [NTDomainName] [varchar](100) NULL,
    [ApplicationName] [varchar](340) NULL,
    [DatabaseName] [nvarchar](4000) NULL,
    [FirstSeen] [datetime] NULL,
    [LastSeen] [datetime] NULL,
    [LogonCount] [bigint] NULL,
) 
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_AuditLogon] ON [dbo].[AuditLogin]
(
    [LoginName] ASC,
    [HostName] ASC,
    [ApplicationName] ASC,
    [DatabaseName] ASC
)
GO

CREATE TABLE [dbo].[AuditLogin_Staging](
    [event_date] [datetime] NULL,
    [original_login] [nvarchar](128) NULL,
    [host_name] [nvarchar](128) NULL,
    [program_name] [nvarchar](255) NULL,
    [database_name] [nvarchar](128) NULL
)
GO

Then you will need a stored procedure to consolidate the data from the staging to the target table:

USE master
GO

CREATE PROCEDURE [dbo].[spConsolidateAuditLogin]
AS
BEGIN

    SET NOCOUNT ON;

    IF OBJECT_ID('tempdb..#AuditLogin_Staging') IS NOT NULL
        DROP TABLE #AuditLogin_Staging;

    CREATE TABLE #AuditLogin_Staging(
        [event_date] [datetime] NULL,
        [original_login] [nvarchar](128) NULL,
        [host_name] [nvarchar](128) NULL,
        [program_name] [nvarchar](255) NULL,
        [database_name] [nvarchar](128) NULL
    );

    DELETE 
    FROM dbo.AuditLogin_Staging
    OUTPUT DELETED.* INTO #AuditLogin_Staging;

    MERGE INTO [AuditLogin] AS AuditLogin
    USING (
        SELECT MAX(event_date), original_login, host_name, program_name, database_name
            ,NtDomainName = CASE SSP.type WHEN 'U' THEN LEFT(SSP.name,CHARINDEX('\',SSP.name,1)-1) ELSE '' END
            ,NtUserName = CASE SSP.type WHEN 'U' THEN RIGHT(SSP.name,LEN(ssp.name) - CHARINDEX('\',SSP.name,1)) ELSE '' END
            ,COUNT(*)
        FROM #AuditLogin_Staging AS ALA
        INNER JOIN sys.server_principals AS SSP
            ON ALA.original_login = SSP.name
        GROUP BY original_login, host_name, program_name, database_name
            ,CASE SSP.type WHEN 'U' THEN LEFT(SSP.name,CHARINDEX('\',SSP.name,1)-1) ELSE '' END
            ,CASE SSP.type WHEN 'U' THEN RIGHT(SSP.name,LEN(ssp.name) - CHARINDEX('\',SSP.name,1)) ELSE '' END
    ) AS src (PostTime,LoginName,HostName,ApplicationName,DatabaseName,NtDomainName,NtUserName,LogonCount)
        ON AuditLogin.ApplicationName = src.ApplicationName
        AND AuditLogin.LoginName = src.LoginName
        AND AuditLogin.HostName = src.HostName
        AND AuditLogin.DatabaseName = src.DatabaseName
    WHEN MATCHED THEN 
        UPDATE SET
             LastSeen   = GETDATE()
            ,LogonCount += src.LogonCount
    WHEN NOT MATCHED THEN
        INSERT (
             LoginName
            ,HostName
            ,NTUserName
            ,NTDomainName
            ,ApplicationName
            ,DatabaseName
            ,FirstSeen
            ,LastSeen
            ,LogonCount
        )
        VALUES (
             src.LoginName
            ,src.HostName
            ,src.NTDomainName
            ,src.NTUserName
            ,src.ApplicationName
            ,src.DatabaseName
            ,src.PostTime
            ,src.PostTime
            ,src.LogonCount
        );
END

The interesting part is the powershell script that reads the events from the session stream. Save the script as c:\capture_logon_events.ps1:

```
[CmdletBinding()]
Param(
[Parameter(Mandatory=$True,Position=1)]
[string]$servername
)

sl $Env:Temp

Add-Type -Path 'C:\Program Files\Microsoft SQL Server\110\Shared\Microsoft.SqlServer.XEvent.Linq.dll'

$connectionString = 'Data Source=' + $servername + '; Initial Catalog = master; Integrated Security = SSP

Code Snippets

CREATE EVENT SESSION [Audit_Logon] ON SERVER 
ADD EVENT sqlserver.LOGIN (
    SET  collect_database_name = (1)    
        ,collect_options_text = (0) 
    ACTION(
         sqlserver.client_app_name
        ,sqlserver.client_hostname
        ,sqlserver.server_principal_name
    )
)
WITH (
     MAX_MEMORY = 4096 KB
    ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
    ,MAX_DISPATCH_LATENCY = 30 SECONDS
    ,MAX_EVENT_SIZE = 0 KB
    ,MEMORY_PARTITION_MODE = NONE
    ,TRACK_CAUSALITY = OFF
    ,STARTUP_STATE = ON
)
GO

CREATE TABLE [dbo].[AuditLogin](
    [LoggingID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    [LoginName] [sysname] NOT NULL,
    [HostName] [varchar](100) NULL,
    [NTUserName] [varchar](100) NULL,
    [NTDomainName] [varchar](100) NULL,
    [ApplicationName] [varchar](340) NULL,
    [DatabaseName] [nvarchar](4000) NULL,
    [FirstSeen] [datetime] NULL,
    [LastSeen] [datetime] NULL,
    [LogonCount] [bigint] NULL,
) 
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_AuditLogon] ON [dbo].[AuditLogin]
(
    [LoginName] ASC,
    [HostName] ASC,
    [ApplicationName] ASC,
    [DatabaseName] ASC
)
GO

CREATE TABLE [dbo].[AuditLogin_Staging](
    [event_date] [datetime] NULL,
    [original_login] [nvarchar](128) NULL,
    [host_name] [nvarchar](128) NULL,
    [program_name] [nvarchar](255) NULL,
    [database_name] [nvarchar](128) NULL
)
GO

USE master
GO

CREATE PROCEDURE [dbo].[spConsolidateAuditLogin]
AS
BEGIN

    SET NOCOUNT ON;

    IF OBJECT_ID('tempdb..#AuditLogin_Staging') IS NOT NULL
        DROP TABLE #AuditLogin_Staging;

    CREATE TABLE #AuditLogin_Staging(
        [event_date] [datetime] NULL,
        [original_login] [nvarchar](128) NULL,
        [host_name] [nvarchar](128) NULL,
        [program_name] [nvarchar](255) NULL,
        [database_name] [nvarchar](128) NULL
    );


    DELETE 
    FROM dbo.AuditLogin_Staging
    OUTPUT DELETED.* INTO #AuditLogin_Staging;




    MERGE INTO [AuditLogin] AS AuditLogin
    USING (
        SELECT MAX(event_date), original_login, host_name, program_name, database_name
            ,NtDomainName = CASE SSP.type WHEN 'U' THEN LEFT(SSP.name,CHARINDEX('\',SSP.name,1)-1) ELSE '' END
            ,NtUserName = CASE SSP.type WHEN 'U' THEN RIGHT(SSP.name,LEN(ssp.name) - CHARINDEX('\',SSP.name,1)) ELSE '' END
            ,COUNT(*)
        FROM #AuditLogin_Staging AS ALA
        INNER JOIN sys.server_principals AS SSP
            ON ALA.original_login = SSP.name
        GROUP BY original_login, host_name, program_name, database_name
            ,CASE SSP.type WHEN 'U' THEN LEFT(SSP.name,CHARINDEX('\',SSP.name,1)-1) ELSE '' END
            ,CASE SSP.type WHEN 'U' THEN RIGHT(SSP.name,LEN(ssp.name) - CHARINDEX('\',SSP.name,1)) ELSE '' END
    ) AS src (PostTime,LoginName,HostName,ApplicationName,DatabaseName,NtDomainName,NtUserName,LogonCount)
        ON AuditLogin.ApplicationName = src.ApplicationName
        AND AuditLogin.LoginName = src.LoginName
        AND AuditLogin.HostName = src.HostName
        AND AuditLogin.DatabaseName = src.DatabaseName
    WHEN MATCHED THEN 
        UPDATE SET
             LastSeen   = GETDATE()
            ,LogonCount += src.LogonCount
    WHEN NOT MATCHED THEN
        INSERT (
             LoginName
            ,HostName
            ,NTUserName
            ,NTDomainName
            ,ApplicationName
            ,DatabaseName
            ,FirstSeen
            ,LastSeen
            ,LogonCount
        )
        VALUES (
             src.LoginName
            ,src.HostName
            ,src.NTDomainName
            ,src.NTUserName
            ,src.ApplicationName
            ,src.DatabaseName
            ,src.PostTime
            ,src.PostTime
            ,src.LogonCount
        );
END

[CmdletBinding()]
Param(
    [Parameter(Mandatory=$True,Position=1)]
    [string]$servername
)

sl $Env:Temp

Add-Type -Path 'C:\Program Files\Microsoft SQL Server\110\Shared\Microsoft.SqlServer.XEvent.Linq.dll'


$connectionString = 'Data Source=' + $servername + '; Initial Catalog = master; Integrated Security = SSPI'

$SessionName = "Audit_Logon"


# create a DataTable to hold login information in memory
$queue = New-Object -TypeName System.Data.DataTable
$queue.TableName = $SessionName

[Void]$queue.Columns.Add("event_date",[DateTime])
[Void]$queue.Columns.Add("original_login",[String])
[Void]$queue.Columns.Add("host_name",[String])
[Void]$queue.Columns.Add("program_name",[String])
[Void]$queue.Columns.Add("database_name",[String])



$last_dump = [DateTime]::Now

# connect to the Extended Events session
[Microsoft.SqlServer.XEvent.Linq.QueryableXEventData] $events = New-Object -TypeName Microsoft.SqlServer.XEvent.Linq.QueryableXEventData `
    -ArgumentList @($connectionString, $SessionName, [Microsoft.SqlServer.XEvent.Linq.EventStreamSourceOptions]::EventStream, [Microsoft.SqlServer.XEvent.Linq.EventStreamCacheOptions]::DoNotCache)

$events | % {
    $currentEvent = $_

    $database_name = $currentEvent.Fields["database_name"].Value
    if($client_app_name -eq $null) { $client_app_name = [string]::Empty }
    $original_login_name = $currentEvent.Actions["server_principal_name"].Value
    $client_app_name = $currentEvent.Actions["client_app_name"].Value
    $client_host_name = $currentEvent.Actions["client_hostname"].Value

    $current_row = $queue.Rows.Add()
    $current_row["database_name"] = $database_name
    $current_row["program_name"] = $client_app_name
    $current_row["host_name"] = $client_host_name
    $current_row["original_login"] = $original_login_name
    $current_row["event_date"] = [DateTime]::Now


    $ts = New-TimeSpan -Start $last_dump -End (get-date)

    # Dump to database every 1 minutes
    if($ts.TotalMinutes -gt 1) {
        $last_dump = [DateTime]::Now

        # BCP data to the staging table master.dbo.master.dbo.AuditLogin_Staging
        $bcp = New-Object -TypeName System.Data.SqlClient.SqlBulkCopy -ArgumentList @($connectionString)
        $bcp.DestinationTableName = "master.dbo.AuditLogin_Staging"
        $bcp.Batchsize = 1000
        $bcp.BulkCopyTimeout = 0

        $bcp.WriteToServer($queue)

        $queue.Rows.Clear()

    }

}

powershell -File C:\capture_logon_events.ps1 -servername $(ESCAPE_DQUOTE(SRVR))

Context

StackExchange Database Administrators Q#141733, answer score: 6

Revisions (0)

No revisions yet.