768
Maintaining a secure and well-performing SQL Server environment requires vigilant monitoring of login activities. T-SQL scripts are a powerful tool for this task, allowing for quick identification of failed login attempts and resolution of developers’ login issues. In this post, you’ll find a simple, yet effective, T-SQL script for monitoring SQL Server login events. Don’t wait – check out the script below and start improving your database’s security and performance today.
T-Sql
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
-- Set the start and end date range for your query
SET @StartDate = GETDATE() - 1 --This sets the start date to 24 hours (1 day) before the current date and time.
SET @EndDate = GETDATE()
-- Create a temporary table to store the error log data
CREATE TABLE #ErrorLogTemp
(
LogDate DATETIME,
ProcessInfo NVARCHAR(50),
LogMessage NVARCHAR(MAX)
)
-- Insert error log data into the temporary table
INSERT INTO #ErrorLogTemp (LogDate, ProcessInfo, LogMessage)
EXEC sp_readerrorlog 0, 1
/*
This line reads the error log and inserts the data into the #ErrorLogTemp table.
sp_readerrorlog 0, 1 means that it reads the current SQL Server error log file,
because 0 indicates the current log
and 1 indicates that it's the SQL Server error log
*/
-- Query for failed login attempts
SELECT
LogDate,
ProcessInfo,
LogMessage
FROM
#ErrorLogTemp
WHERE
LogDate BETWEEN @StartDate AND @EndDate
AND LogMessage LIKE 'Login failed%'
ORDER BY
LogDate DESC
-- Drop the temporary table
DROP TABLE #ErrorLogTemp
