How to verify if a SQL Account is still used? We have a SQL Server Login on a SQL Server 2008, and no one seems to know if the account is actually being used or not. It is assumed it is being used, as it is configured in a Linked_Server connection, however no one knows if it is still really being used, and if so for what. How do we verify, aside from disabling and waiting for a squeal?
An additional, easy, option is to log all successful &amp; failed logins.  Under the server properties tab



This will write any use (or attempted use) of the login to the SQL Server log.  From there you can programatically search the log.

USE Test
GO

-- 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) = 'UserName', 
-- P3 is a value to search on
@p4 NVARCHAR(255) = ''
-- P4 is another search value


IF OBJECT_ID('IOErrorLog') IS NULL
    CREATE TABLE IOErrorLog (
            ServerName nvarchar(256), 
            LogFileName nvarchar(4000),
            Occurances int,
            LogDate datetime,
            ProcessInfo nvarchar(50),
            ErrorText nvarchar(max),
        CONSTRAINT pk_IOErrorLog PRIMARY KEY (ServerName, LogDate, LogFileName)
    )

----------------------------------------------------------------------
-- 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 &lt; @NumberOfLogfiles
BEGIN

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), 
        SUBSTRING(ErrorText, 27, patindex('%occurrence%',ErrorText)-28),
        LogDate, ProcessInfo, ErrorText
    FROM #temp
    WHERE #temp.LogDate NOT IN (
        SELECT LogDate
        FROM IOErrorLog
        WHERE ServerName = @@SERVERNAME)

DROP TABLE #temp

SELECT * FROM IOErrorLog


The down side here is that you are logging the login successes &amp; failures for everyone.  It could bloat your error log a bit.  It's generally considered a best practice to pull at least the failed logins though.