Query to fetch inactive users in sql server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Query to fetch inactive users in sql server

Author

Topic

mkfrns
Starting Member

2 Posts

Posted - 2013-12-10 : 09:20:43

Could any1 help me with query which fetches the inactive users and their last logged in date in sql server?

LPCPT
Starting Member

1 Post

Posted - 2013-12-18 : 01:59:42

Try this. It checks the error logs that's available. This assumes that successful logins are logged.
Declare @period int --in days
set @period = 7
--Get all available logs
Create table #Logs ( Archivenr int, Logdate datetime, Size int)
Insert #Logs
Exec XP_ENUMERRORLOGS
--select * from #Logs
--drop table #Logs

--Loop through logs and extract successfull logins
Create table ##ErrLog (logdate datetime, processinfo varchar(255), LoginStr varchar(1500))
Declare @SqlCmd nvarchar(4000), @LogNr int

Declare LogCurs Cursor for Select Archivenr from #Logs
where datediff(day, logdate, getdate()) <= @period

Open LogCurs
Fetch next from LogCurs into @LogNr
While @@Fetch_Status = 0
Begin
Set @SqlCmd = 'Insert ##ErrLog Exec master.dbo.xp_readerrorlog ' +
Convert(varchar(4), @LogNr) + ', 1 , ''login succeeded'''
Exec (@SqlCmd)
Fetch next from LogCurs into @LogNr
End

Close LogCurs
Deallocate LogCurs

--We only need entries for last x days (@period days)

--Fix LoginStr to only contain the login name that will be in single quotes in the message
Update ##ErrLog set LoginStr = Substring(LoginStr, charindex('''', LoginStr, 1)+1,
charindex('''', LoginStr, charindex('''', LoginStr, 1)+1)- charindex('''', LoginStr, 1)-1)

--Get loginid, login name and last login attempt date for logins that did not log in
--in the last 3 months

Select Name, Pre.last_logdate, hasaccess, isntname, isntuser, sysadmin
From
--Get all logins with no login entry in last @period days
(select SP.Name, hasaccess, isntname, isntuser, sysadmin
from sys.server_principals SP
inner join syslogins SL on SP.Sid = SL.Sid
where SP.Sid <> 0x01 --sa account
and type_desc in ('SQL_Login', 'WINDOWS_LOGIN')
and SL.name not in
(select LoginStr from ##ErrLog where datediff(day, logdate, getdate()) <= @period)
--and status <> 10
) as Post
Left outer join
--Get last logdate before last 90 days
(Select LoginStr, max(logdate) as last_logdate from ##ErrLog
--where datediff(day, logdate, getdate()) > @period
group by LoginStr) as Pre
On Post.name = Pre.LoginStr

drop table #Logs
drop table ##ErrLog

Integrity rules

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources