July 24, 2012 at 8:33 am
Hi all,
I'm trying my best to look after a SQL Server 2008 instance, relatively new to all that sort of thing, so am struggling with something.
There are a large number of SQL Logins configured, but there's a good chance a lot of them haven't been used for a while.
Is there an easy way to detect what logins have been used in the last, say, 90 days? Ideally I'd like the results in some sort of report, or easily readable text file, rather than having to manually scan through the even logs.
Thanks
July 24, 2012 at 8:38 am
You would need to create some custom logging in the form of a login trigger, which inserts into a table when a user logs in.
From there you could link it to master.sys.syslogins where the login is not in the table to see which are unused.
CREATE TRIGGER LogTheLoginUsername
ON ALL SERVER
FOR LOGON
AS
BEGIN
INSERT INTO sometable (username)
SELECT
ORIGINAL_LOGIN()
END
July 24, 2012 at 9:30 am
I did a bit of digging and found this script, located here - http://sequelserver.blogspot.com/2011/06/lists-user-login-attempts-to-sql-server.html
DECLARE @TSQL NVARCHAR(2000)
DECLARE @lC INT
CREATE TABLE #TempLog (
LogDate DATETIME,
ProcessInfo NVARCHAR(50),
[Text] NVARCHAR(MAX))
CREATE TABLE #logF (
ArchiveNumber INT,
LogDate DATETIME,
LogSize INT
)
INSERT INTO #logF
EXEC sp_enumerrorlogs
SELECT @lC = MIN(ArchiveNumber) FROM #logF
WHILE @lC IS NOT NULL
BEGIN
INSERT INTO #TempLog
EXEC sp_readerrorlog @lC
SELECT @lC = MIN(ArchiveNumber) FROM #logF
WHERE ArchiveNumber > @lC
END
--Failed login counts. Useful for security audits.
SELECT 'Failed - ' + CONVERT(nvarchar(5), COUNT(Text)) + ' attempts' AS [Login Attempt], Text AS Details
FROM #TempLog
where ProcessInfo = 'Logon'
and Text like '%failed%'
Group by Text
--Find Last Successful login. Useful to know before deleting "obsolete" accounts.
SELECT Distinct 'Successful - Last login at (' + CONVERT(nvarchar(64), MAX(LogDate)) + ')' AS [Login Attempt], Text AS Details
FROM #TempLog
where ProcessInfo = 'Logon' and Text like '%succeeded%'
and Text not like '%NT AUTHORITY%'
Group by Text
DROP TABLE #TempLog
DROP TABLE #logF
On my server all I get is the failed logins section so I might have something not configured correctly but it does look promising to help you with your problem.
Bill
July 24, 2012 at 11:57 am
bill.schoonmaker (7/24/2012)
I did a bit of digging and found this script, located here - http://sequelserver.blogspot.com/2011/06/lists-user-login-attempts-to-sql-server.html
DECLARE @TSQL NVARCHAR(2000)
DECLARE @lC INT
CREATE TABLE #TempLog (
LogDate DATETIME,
ProcessInfo NVARCHAR(50),
[Text] NVARCHAR(MAX))
CREATE TABLE #logF (
ArchiveNumber INT,
LogDate DATETIME,
LogSize INT
)
INSERT INTO #logF
EXEC sp_enumerrorlogs
SELECT @lC = MIN(ArchiveNumber) FROM #logF
WHILE @lC IS NOT NULL
BEGIN
INSERT INTO #TempLog
EXEC sp_readerrorlog @lC
SELECT @lC = MIN(ArchiveNumber) FROM #logF
WHERE ArchiveNumber > @lC
END
--Failed login counts. Useful for security audits.
SELECT 'Failed - ' + CONVERT(nvarchar(5), COUNT(Text)) + ' attempts' AS [Login Attempt], Text AS Details
FROM #TempLog
where ProcessInfo = 'Logon'
and Text like '%failed%'
Group by Text
--Find Last Successful login. Useful to know before deleting "obsolete" accounts.
SELECT Distinct 'Successful - Last login at (' + CONVERT(nvarchar(64), MAX(LogDate)) + ')' AS [Login Attempt], Text AS Details
FROM #TempLog
where ProcessInfo = 'Logon' and Text like '%succeeded%'
and Text not like '%NT AUTHORITY%'
Group by Text
DROP TABLE #TempLog
DROP TABLE #logF
On my server all I get is the failed logins section so I might have something not configured correctly but it does look promising to help you with your problem.
Bill
this script goes through the log files and looks for the event type of logon and the text of succeeded or failed. i would guess you only have audit failed logon and not audit all logon's. in which case you would only see the failed logons since thats all that would be in the log.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
July 25, 2012 at 2:19 am
Thanks for everyone's input... however, this is not really what I'm looking for.
The script above will list all user activity, but it's essentially just duplicating what I can already see in the SQL log files. I think what I want is something that will just list the most recent login by the user, with each user account is only listed once (I'd then have to c&p the data into Excel and order by date, most probably).
Is this even possible?
July 25, 2012 at 2:56 am
A logon trigger or a trace for audit login which logs to a trace file or a trace table which you can then query to see who logs into the server would be the only options if you dont want to look through the logs.
There is no login history table unless you manually create one using a logon trigger.
July 25, 2012 at 6:45 am
I'm afraid that looks to be beyond my ability.
July 25, 2012 at 6:55 am
The output of the script I posted could easily be inserted into a table and then you could use that table for your report. It doesn't list the detail, it only lists the total number of failed logins by username and incoming ip/machine and then for the successful logins it lists the last time they logged in. You could bump this up against the users you have and after 90 days start dropping folks that haven't logged in.
As for the trigger option, a few of the articles I found regarding that solution said don't do it so be careful. Mainly because if the trigger ever fails you can lock yourself out.
See these
http://msdn.microsoft.com/en-us/library/bb326598.aspx
http://qa.sqlservercentral.com/articles/SQLServerCentral/sqlserver2005logontriggers/2366/
http://www.pythian.com/news/1310/sql-server-troubleshooting-logon-triggers/
Good luck!
July 25, 2012 at 6:56 am
You could disable the logins one at a time and see if anyone complains! 😀
A logon trigger or switching on security audit for successful logins (which will supersize your event log very quickly!) are pretty much your only choices here unfortunately.
July 25, 2012 at 7:10 am
Logon triggers = beyond me. I think.
I can't see how it can be done any other way. If indeed it can be done that way, of course.
Anything that involves scanning the output manually is out the question really. The margin for error would be too great, and the effort involved would be too much, when we have hundreds of logons to the application every day.
July 25, 2012 at 7:13 am
My origininal post contains a default login trigger, all you need to do is create a table with 1 column and change the trigger to insert into that column.
July 25, 2012 at 8:06 am
Be very, very careful with FOR LOGON triggers. If you take the first script in this thread as an example, it'll work fine but should anything happen to the logging table, or any change happen that causes that trigger to return an error, you will lock out all users of the database including yourself.
This is because when the error is detected, the calling event (the LOGON) is rolled back.
You could perhaps create a job that polls sys.dm_exec_connections or sys.dm_exec_sessions for the login name every X seconds, and updates a table. I.e.
(do this once)
CREATE TABLE loginAudit (
login_name VARCHAR(MAX), last_login_date DATETIME)
INSERT INTO loginAudit (login_name, last_login_date)
SELECT DISTINCT sl.name, NULL FROM sys.syslogins sl
(this is the job code)
SELECT MAX(login_time) [login_time], login_name INTO #loginTempTable
FROM sys.dm_exec_sessions
GROUP BY login_name
UPDATE loginAudit
SET last_login_date = ltt.login_time
FROM #loginTempTable ltt
WHERE loginAudit.login_name = ltt.login_name
Schedule this snippet as a Job in SQL Server Agent and run it e.g. every 30 seconds.
Then checking the last login date for every user is as simple as querying the loginAudit table.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
July 26, 2012 at 2:00 am
Thanks.
I'll give that a go.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply