Sql-server – SQL unused logins

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 = 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()

    }

}

The script can be run by a SQLAgent powershell step in a job. Create a job, set it to run when SQLAgent starts, add a powershell step with this code:

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

Then you need another job to consolidate the events using the stored procedure: set it to run every 5 minutes and add a T-SQL step with the call to the consolidate stored procedure.

EXEC [dbo].[spConsolidateAuditLogin]

And you're done. Looks complicated, but it's not.