January 21, 2009 at 3:32 pm
I've inherited a number of SQL servers. There appear to be a number of users who are no longer with the company and therefore should never be logging in. However, I'm never sure if there's an app on our network with embedded credentials. Is there any way to know that a user account has not been used for a long time?
Thanks
January 21, 2009 at 3:50 pm
Like this:
select * FROM INFORMATION_SCHEMA.PARAMETERS
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 21, 2009 at 4:14 pm
Thanks for the response - That query provides lots of information but nothing to do with users and access times. Maybe I just don't know how to use it?
January 21, 2009 at 4:46 pm
Heh. No, you're fine. I stink. 🙁
It seems that I cut and pasted the wrong query. Sorry, my bad.
Try this one instead.
select name, accdate from sys.syslogins
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 21, 2009 at 7:57 pm
The column accdate is the created date, not the last time the account was accessed. You can see this by doing an sp_helptext of syslogins:
CREATE VIEW sys.syslogins AS SELECT
sid = p.sid,
status = convert(smallint, 8 +
CASE WHEN m.state in ('G','W') THEN 1 ELSE 2 END),
createdate = p.create_date,
updatedate = p.modify_date,
accdate = p.create_date,
.
.
.
FROM sys.server_principals p LEFT JOIN master.sys.sysprivs m
ON m.class = 100 AND m.id = 0 AND m.subid = 0 AND m.grantee = p.principal_id AND m.grantor = 1 AND m.type = 'COSQ'
WHERE p.type <> 'R'
Unfortunately, SQL Server does not record the last login date and time. You can run a server trace auditing for login success and failure, but you'll need to then go through it.
K. Brian Kelley
@kbriankelley
January 22, 2009 at 11:19 am
Thanks for the responses. I will look into using trace. I was hoping to find something built in but you can't win them all!
later
January 23, 2009 at 2:47 am
In SQL 2005 SP2 or higher you can also create a Login trigger to log the logon event into a table.
[font="Verdana"]Markus Bohse[/font]
January 23, 2009 at 6:12 am
MarkusB (1/23/2009)
In SQL 2005 SP2 or higher you can also create a Login trigger to log the logon event into a table.
You can, but if something were to happen to the table, no one logs in. Been there, heard the screams, hence the reason I recommended the trace. Server side trace would be lightweight, you could really restrict down the columns, and initially it'll write to a file, but you can import that file into a trace table and do your queries that way.
K. Brian Kelley
@kbriankelley
January 23, 2009 at 7:40 am
K. Brian Kelley (1/23/2009)
MarkusB (1/23/2009)
In SQL 2005 SP2 or higher you can also create a Login trigger to log the logon event into a table.You can, but if something were to happen to the table, no one logs in. Been there, heard the screams, hence the reason I recommended the trace. Server side trace would be lightweight, you could really restrict down the columns, and initially it'll write to a file, but you can import that file into a trace table and do your queries that way.
Maybe I was just lucky, but so far my implementations of Login triggers worked fine. But I totally understand your point. I think it's also a choice between 100% auditing vs. availability of the database..
[font="Verdana"]Markus Bohse[/font]
January 23, 2009 at 8:40 pm
MarkusB (1/23/2009)
In SQL 2005 SP2 or higher you can also create a Login trigger to log the logon event into a table.
Why SP2? I thought that Login Triggers have been in SQL Server 2005 since RTM?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 24, 2009 at 7:40 am
RBarryYoung (1/23/2009)
MarkusB (1/23/2009)
In SQL 2005 SP2 or higher you can also create a Login trigger to log the logon event into a table.Why SP2? I thought that Login Triggers have been in SQL Server 2005 since RTM?
No, while DDL Triggers where already implemented in RTM, Login Triggers where new in SP2.
[font="Verdana"]Markus Bohse[/font]
January 24, 2009 at 8:55 am
Good to know, thanks.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 24, 2009 at 9:01 am
since you're at it ... this is what I experienced with logon triggers : http://www.sqlservercentral.com/articles/Administration/64974/
With regards to the login info:
Select name
, create_date
, modify_date
, default_database_name
, is_policy_checked
, is_expiration_checked
, LOGINPROPERTY(name, 'DaysUntilExpiration') DaysUntilExpiration
, LOGINPROPERTY(name, 'PasswordLastSetTime') PasswordLastSetTime
, LOGINPROPERTY(name, 'IsLocked') IsLocked
, LOGINPROPERTY(name, 'IsExpired') IsExpired
, LOGINPROPERTY(name, 'BadPasswordCount') BadPasswordCount
, LOGINPROPERTY(name, 'BadPasswordTime') BadPasswordTime
, LOGINPROPERTY(name, 'HistoryLength') HistoryLength
, LOGINPROPERTY(name, 'IsMustChange') IsMustChange
, LOGINPROPERTY(name, 'LockoutTime') LockoutTime
, LOGINPROPERTY(name, 'PasswordLastSetTime') PasswordLastSetTime
, LOGINPROPERTY(name, 'PasswordHash') PasswordHash
From sys.sql_logins ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 9, 2009 at 7:17 am
Question regarding the [modify_date], I thought this would be similar to SQL 2000's xdate2. However it seems that it behaves like the last time the user access the SQL Server directly Am I wrong?
October 12, 2009 at 2:24 am
BOL sql2005 topic "sys.server_principals" states:
modify_date datetime Time at which the principal definition was last modified.
With all my sql2005(sp3) instances, it contains the actual modification date of the principal.
e.g. password changed, pollicy settings changed.
We have connection tracking on all our sql instances and I can confirm these times are not modified when the user logs on to the instance.
( www.sqlservercentral.com/articles/Administration/64974/ )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply