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?
Showing posts with label accomplish. Show all posts
Showing posts with label accomplish. Show all posts
Monday, March 19, 2012
Last Logins
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?
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?
Subscribe to:
Posts (Atom)