Last Logon Date

Is there a system table that shows the last logon date for a login?
Or is there a way to setup an alert or something to keep track of logon date
for a login?
Thanks,
Ken
Ken,
You can trace this using Profiler or you can set up auditing for successful
and/or failed login attempts on the EM - Server Properties - Security tab -
Audit Level.
HTH
Jerry
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:806D9945-7352-49F3-B43C-97B3F838F9F2@.microsoft.com...
> Is there a system table that shows the last logon date for a login?
> Or is there a way to setup an alert or something to keep track of logon
> date
> for a login?
> Thanks,
> Ken
|||I setup an alert for error 18453 Login succeeded trusted connection.
This works fine -- I get notified by an email. But I want to execute a job
from this alert to save the login information into a table so that I can
remove the login if it has not been used for a number of months. This is
part of our security audit.
My question is -- how can I pass the login info from an alert to a job?
Thanks,
Ken
"Jerry Spivey" wrote:

> Ken,
> You can trace this using Profiler or you can set up auditing for successful
> and/or failed login attempts on the EM - Server Properties - Security tab -
> Audit Level.
> HTH
> Jerry
> "Ken" <Ken@.discussions.microsoft.com> wrote in message
> news:806D9945-7352-49F3-B43C-97B3F838F9F2@.microsoft.com...
>
>

Last Logon Date

Is there a system table that shows the last logon date for a login?
Or is there a way to setup an alert or something to keep track of logon date
for a login?
Thanks,
KenKen,
You can trace this using Profiler or you can set up auditing for successful
and/or failed login attempts on the EM - Server Properties - Security tab -
Audit Level.
HTH
Jerry
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:806D9945-7352-49F3-B43C-97B3F838F9F2@.microsoft.com...
> Is there a system table that shows the last logon date for a login?
> Or is there a way to setup an alert or something to keep track of logon
> date
> for a login?
> Thanks,
> Ken|||I setup an alert for error 18453 Login succeeded trusted connection.
This works fine -- I get notified by an email. But I want to execute a job
from this alert to save the login information into a table so that I can
remove the login if it has not been used for a number of months. This is
part of our security audit.
My question is -- how can I pass the login info from an alert to a job?
Thanks,
Ken
"Jerry Spivey" wrote:

> Ken,
> You can trace this using Profiler or you can set up auditing for successfu
l
> and/or failed login attempts on the EM - Server Properties - Security tab
-
> Audit Level.
> HTH
> Jerry
> "Ken" <Ken@.discussions.microsoft.com> wrote in message
> news:806D9945-7352-49F3-B43C-97B3F838F9F2@.microsoft.com...
>
>

Last Logon Date

Is there a system table that shows the last logon date for a login?
Or is there a way to setup an alert or something to keep track of logon date
for a login?
Thanks,
KenKen,
You can trace this using Profiler or you can set up auditing for successful
and/or failed login attempts on the EM - Server Properties - Security tab -
Audit Level.
HTH
Jerry
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:806D9945-7352-49F3-B43C-97B3F838F9F2@.microsoft.com...
> Is there a system table that shows the last logon date for a login?
> Or is there a way to setup an alert or something to keep track of logon
> date
> for a login?
> Thanks,
> Ken|||I setup an alert for error 18453 Login succeeded trusted connection.
This works fine -- I get notified by an email. But I want to execute a job
from this alert to save the login information into a table so that I can
remove the login if it has not been used for a number of months. This is
part of our security audit.
My question is -- how can I pass the login info from an alert to a job?
Thanks,
Ken
"Jerry Spivey" wrote:
> Ken,
> You can trace this using Profiler or you can set up auditing for successful
> and/or failed login attempts on the EM - Server Properties - Security tab -
> Audit Level.
> HTH
> Jerry
> "Ken" <Ken@.discussions.microsoft.com> wrote in message
> news:806D9945-7352-49F3-B43C-97B3F838F9F2@.microsoft.com...
> > Is there a system table that shows the last logon date for a login?
> > Or is there a way to setup an alert or something to keep track of logon
> > date
> > for a login?
> >
> > Thanks,
> > Ken
>
>

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'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?You can enable success level auditing (without needing C2) so that every time
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 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?|||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?

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?