Last Logins

To anyone who could help:
Let me explain what I'm trying to accomplish. We now have a business
requirement that we need to track last logins on all of our SQL servers. I'v
e
been searching, reading, googling everything that I can think of and there i
s
no real defined way of doing this. Some suggested using C2 auditing, but tha
t
is too intrusive and caused a performance hit on our servers. My perfect
solution would be to have it either:
1. Go into a new table that I could query
2. Update the xdate2 in sysxlogins (even though this not recommended, I'm
just trying to find a solution)
3. Update a flat file
Does anyone have any suggestions?You can enable success level auditing (without needing C2) so that every tim
e
a connection is established to SQL Server it is logged to the SQL Server
Errorlog ie.
Login succeeded for user 'sa'. Connection: Non-Trusted. or Login succeeded
for user 'WARDYIT01\pward'. Connection: Trusted.
To enable auditing in Enterprise Manager Right Click on your SQL Server
instance and select Properties. Select the security tab and select the
Success or All radio buttons. Note: SQL Server will need to be restart for
this to take effect.
You could then populate a table on a scheduled basis with the contents of
the SQL Server Errorlog and query this table. Below is a quick illustration
of how this could be done.
CREATE TABLE #audit
(
ERRORLOG VARCHAR(2000),
ContinuationRow INT
)
INSERT #audit EXEC xp_readerrorlog
SELECT MAX(SUBSTRING(ERRORLOG, 1, 22)) AS LastLogin
FROM #audit
WHERE ERRORLOG LIKE '%Login succeeded for user ''sa''%'
HTH
- Peter Ward
WARDY IT Solutions
"Big Ern" wrote:

> To anyone who could help:
> Let me explain what I'm trying to accomplish. We now have a business
> requirement that we need to track last logins on all of our SQL servers. I
've
> been searching, reading, googling everything that I can think of and there
is
> no real defined way of doing this. Some suggested using C2 auditing, but t
hat
> is too intrusive and caused a performance hit on our servers. My perfect
> solution would be to have it either:
> 1. Go into a new table that I could query
> 2. Update the xdate2 in sysxlogins (even though this not recommended, I'm
> just trying to find a solution)
> 3. Update a flat file
> Does anyone have any suggestions?|||Set up a server side tracing for "Audit".. You can use SQL profiler to
generate the scripts..
Jayesh
"Big Ern" <BigErn@.discussions.microsoft.com> wrote in message
news:3C4CCD66-CB0B-46DD-9CFE-CE2C73885E54@.microsoft.com...
> To anyone who could help:
> Let me explain what I'm trying to accomplish. We now have a business
> requirement that we need to track last logins on all of our SQL servers.
> I've
> been searching, reading, googling everything that I can think of and there
> is
> no real defined way of doing this. Some suggested using C2 auditing, but
> that
> is too intrusive and caused a performance hit on our servers. My perfect
> solution would be to have it either:
> 1. Go into a new table that I could query
> 2. Update the xdate2 in sysxlogins (even though this not recommended, I'm
> just trying to find a solution)
> 3. Update a flat file
> Does anyone have any suggestions?