|
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 |
 |
|
Query to fetch inactive users in sql server