SQL Clone
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
Old Hand
Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)

Group: General Forum Members
Points: 355 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
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36804 Visits: 9518
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
Old Hand
Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)

Group: General Forum Members
Points: 355 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
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36804 Visits: 9518
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 (26K reputation)

Group: Moderators
Points: 26148 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
Old Hand
Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)

Group: General Forum Members
Points: 355 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10989 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 (26K reputation)

Group: Moderators
Points: 26148 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10989 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
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36804 Visits: 9518
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