Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Determine user's last login


Determine user's last login

Author
Message
DoubleEx
DoubleEx
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 237
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
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
Like this:

select * FROM INFORMATION_SCHEMA.PARAMETERS



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
DoubleEx
DoubleEx
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 237
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?
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
Heh. No, you're fine. I stink. Sad

It seems that I cut and pasted the wrong query. Sorry, my bad.

Try this one instead.

select name, accdate from sys.syslogins



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6816 Visits: 1917
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
DoubleEx
DoubleEx
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 237
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
MarkusB
MarkusB
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4449 Visits: 4208
In SQL 2005 SP2 or higher you can also create a Login trigger to log the logon event into a table.

Markus Bohse
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6816 Visits: 1917
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
MarkusB
MarkusB
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4449 Visits: 4208
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..

Markus Bohse
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
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?

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search