Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Determine user's last login Expand / Collapse
Author
Message
Posted Wednesday, January 21, 2009 3:32 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 16, 2014 4:23 AM
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
Post #641210
Posted Wednesday, January 21, 2009 3:50 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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."
Post #641231
Posted Wednesday, January 21, 2009 4:14 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 16, 2014 4:23 AM
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?
Post #641242
Posted Wednesday, January 21, 2009 4:46 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #641256
Posted Wednesday, January 21, 2009 7:57 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #641335
Posted Thursday, January 22, 2009 11:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 16, 2014 4:23 AM
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
Post #641909
Posted Friday, January 23, 2009 2:47 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:00 AM
Points: 4,427, Visits: 4,175
In SQL 2005 SP2 or higher you can also create a Login trigger to log the logon event into a table.

Markus Bohse
Post #642346
Posted Friday, January 23, 2009 6:12 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #642402
Posted Friday, January 23, 2009 7:40 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:00 AM
Points: 4,427, Visits: 4,175
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
Post #642467
Posted Friday, January 23, 2009 8:40 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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."
Post #642909
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse