5

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 ?

6

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.

  • what if the extended events run continuously and the .xel files become big, how to address that issue ? – jason Aug 1 '16 at 17:17
  • This solution does not rely on storing events in files. Events are captured on the fly, no xel file needed – spaghettidba Aug 1 '16 at 17:19
  • I tried testing the same thing above the powershell script didn't execute thru Sql job it got failed. " Incorrect syntax near the keyword 'File'" – jason Aug 2 '16 at 16:21
  • I suspect that you didn't choose the correct step type. – spaghettidba Aug 2 '16 at 16:25
4

Based on some code here I put together the following script:

Note: It makes a couple of assumptions. 1) You are logging successful logins (and don't care about failed ones) 2) You are keeping enough log files to cover the last 90 days.

CREATE TABLE #IOErrorLog (
        ServerName nvarchar(256), 
        UserName nvarchar(4000),
        LogDate datetime,
        ProcessInfo nvarchar(50),
        ErrorText nvarchar(max)
)

----------------------------------------------------------------------
-- This part of the code was found here: 
-- https://ask.sqlservercentral.com/questions/99484/number-of-error-log-files.html

DECLARE @FileList AS TABLE (
    subdirectory NVARCHAR(4000) NOT NULL
    ,DEPTH BIGINT NOT NULL
    ,[FILE] BIGINT NOT NULL
);

DECLARE @ErrorLog NVARCHAR(4000), @ErrorLogPath NVARCHAR(4000);
SELECT @ErrorLog = CAST(SERVERPROPERTY(N'errorlogfilename') AS NVARCHAR(4000));
SELECT @ErrorLogPath = SUBSTRING(@ErrorLog, 1, LEN(@ErrorLog) - CHARINDEX(N'\', REVERSE(@ErrorLog))) + N'\';

INSERT INTO @FileList
EXEC xp_dirtree @ErrorLogPath, 0, 1;

DECLARE @NumberOfLogfiles INT;
SET @NumberOfLogfiles = (SELECT COUNT(*) FROM @FileList WHERE [@FileList].subdirectory LIKE N'ERRORLOG%');
-- SELECT @NumberOfLogfiles;
----------------------------------------------------------------------

CREATE TABLE #temp (
    LogDate datetime, 
    ProcessInfo nvarchar(500), 
    ErrorText nvarchar(max))

DECLARE @p1 INT = 0

--WHILE @p1 < @NumberOfLogfiles
--BEGIN
    -- P1 is the file number starting at 0
    DECLARE 
    @p2 INT = 1, 
    -- P2 1 for SQL logs, 2 for SQL Agent logs
    @p3 NVARCHAR(255) = 'Login succeeded', 
    -- P3 is a value to search on
    @p4 NVARCHAR(255) = ''
    -- P4 is another search value

BEGIN TRY
    INSERT INTO #temp 
    EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4 
END TRY
BEGIN CATCH
    PRINT 'Error occurred processing file ' + cast(@p1 as varchar(10))
END CATCH

--SET @p1 = @p1 + 1
--END
INSERT INTO #IOErrorLog
     SELECT DISTINCT @@SERVERNAME, 
        SUBSTRING(ErrorText, charindex('''',ErrorText)+1, 
        charindex('''',ErrorText,charindex('''',ErrorText)+1)-charindex('''',ErrorText)-1),
        LogDate, ProcessInfo, ErrorText
    FROM #temp
    WHERE #temp.LogDate NOT IN (
        SELECT LogDate
        FROM #IOErrorLog
        WHERE ServerName = @@SERVERNAME)



DROP TABLE #temp
--DROP TABLE #IOErrorLog

select UserName, MAX(LogDate) From #IOErrorLog GROUP BY UserName
2

I would suggest using SQL Audit if you want to be sure. Take a look at this https://msdn.microsoft.com/en-us/library/cc280663.aspx

You would want to audit the SUCCESSFUL_LOGIN_GROUP group.

Mat

  • This is EE only though. – Martin Smith Jun 20 '16 at 16:34
1

Possible duplicate of https://stackoverflow.com/questions/1905227/date-of-last-login-or-read-operation-on-a-sql-server-database

This query should show the last login date/time and login_name. More information can be gathered by querying sys.dm_exec_sessions.

select max (login_time)as last_login_time, login_name from sys.dm_exec_sessions
group by login_name;
  • It is likely that most sessions started in the last 90 days will no longer be active and show up in there. – Martin Smith Jun 20 '16 at 16:06
  • 1
    Remember that most DMVs only show information since the last instance re-start and that one in general is much more volatile. It only contains existing connections, not any history. – Kenneth Fisher Jun 20 '16 at 16:07
  • This only give you active sessions, if I log into a server via SSMS it will show up in this query, if I then disconnect then it won't appear in that query any more. – Mat Jun 20 '16 at 16:29
0
        USE test;
        GO
        CREATE TABLE LogonAudit
        (
        AuditID INT NOT NULL CONSTRAINT PK_LogonAudit_AuditID 
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
        , UserName NVARCHAR(255)
        , LogonDate DATETIME
        , spid INT NOT NULL
        );
        GO
        GRANT INSERT ON Test.dbo.LogonAudit TO public;
        GO


   create TRIGGER MyLogonTrigger ON ALL SERVER FOR LOGON
   AS 
   BEGIN
   IF SUSER_SNAME() <> 'sa'
   INSERT INTO Test.dbo.LogonAudit (UserName, LogonDate, spid) 
        VALUES (SUSER_SNAME(), GETDATE(), @@SPID);
   END;
   GO

   ENABLE TRIGGER MyLogonTrigger ON ALL SERVER;

I tried to use logon trigger, but with this trigger I get failed login for NT\reportserver, other SQL ids. also it is not capturing the sql authentications

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.