tables.
Anith|||Hi Mat
Thank you for using MSDN newsgroup and it is my pleasure to help you with
your issue.
SQL Server will save all the login account information in system table
'syslogins' in database 'master'. Base on my knowledge, SQL Server will not
keep the change of the logins history. However, the c2 audit in SQL Server
2000 is to review both successful and unsuccessful attempts to access
statements and objects. With this information, you can document system
activity and look for the history of this object. Another approach could be
add a trigger on the 'syslogins' table, and all the modification on table
'syslogins' could be saved in your own table for analysis.
You could refer to the following parts in SQL Server Books Online:
'syslogins system table', 'c2 auditing', C2 audit mode Option' and 'trigger'
Hope this helps and if you still have questions about it, please feel free
to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Baisong,
[QUOTE]
history of this object. <<
With c2 audit trace logs, attempts with successes & failures can be
captured. However, unlike policy violations, changes in login cannot be
reliably captured with this information.
[QUOTE]
all the modification on table 'syslogins' could be saved in your own table
for analysis.<<
First, syslogins is not a table, but a view in the master database.
Irrespective of that, in general, creating a trigger on a system table is
not supported in SQL Server.
So, one can create an INSTEAD OF trigger on this view, but clearly it would
be risky to rely on this solution since certain information within are
undocumented and/or reserved for internal use.
Anith|||Hello Mat,
Considering your requirement, C2 Audit or Server Side Profiler Traces
are your options to track the
sql server login changes.
When C2-level auditing is enabled, the system uses a default profile
of auditable events to determine what to log. You can use SQL Server
Profiler to view the log files that SQL Server creates. However, as
Anith has pointed out, changes in logins cannot be reliably captured
with the C2 audit feature, since disadvantage of using SQL Server
2000's C2 auditing functionality is its all-or-nothing approach. You
can't instruct SQL Server which event categories to audit.
The better option here would be to use Server Side Profiler Traces
which traces the "Security Audit" EventClass.
e.g. if you trace the "Audit Object Permission" even then, when the
SQL Login in added or dropped , the Text Data column should give you
corresponding sp_addlogin/sp_droplogin <login name> and the Start
Date/End Date should give you the time of these events etc..
You can save these traces to a file to keep the Login event history.
For further info about different security audits in SQL Server 2K,
please refer to the following article,
SQL Server 2000 Auditing
http://www.microsoft.com/technet/tr...rl=/technet/sec
urity/prodtech/dbsql/sql2kaud.asp?frame=true
Does that answer your question ?
Thanks for using MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
--
quote:
>From: "Anith Sen" <anith@.bizdatasolutions.com>
>References: <29ca01c3e1c4$30dfaa70$a301280a@.phx.gbl>
<kVmC0rg5DHA.3496@.cpmsftngxa07.phx.gbl>
quote:
>Subject: Re: Logins
>Date: Thu, 29 Jan 2004 09:50:00 -0600
>Lines: 25
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>Message-ID: <#$zQi#n5DHA.2064@.TK2MSFTNGP11.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: 64-132-242-82.gen.twtelecom.net 64.132.242.82
>Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT
NGP11.phx.gbl
quote:
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:326558
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Baisong,
>
for the[QUOTE]
>history of this object. <<
>With c2 audit trace logs, attempts with successes & failures can be
>captured. However, unlike policy violations, changes in login cannot
be
quote:
>reliably captured with this information.
>
table, and[QUOTE]
>all the modification on table 'syslogins' could be saved in your own
table
quote:
>for analysis.<<
>First, syslogins is not a table, but a view in the master database.
>Irrespective of that, in general, creating a trigger on a system
table is
quote:
>not supported in SQL Server.
>So, one can create an INSTEAD OF trigger on this view, but clearly
it would
quote:
>be risky to rely on this solution since certain information within
are
quote:
>undocumented and/or reserved for internal use.
>--
>Anith
>
>
No comments:
Post a Comment