Any way to run a invisible trace on Security Audit?

Is there anyway I could run an Audit trace on SQl Server which records SQL Server System Admin Login/Logout , failed login and machine names I don't want the trace window to show on screen however would like a file generated for later viewing. Also due to Firewall issues we have, We don't have SQL tools enabled to connect to that server.Yes, you can configure SQL 2000 Auditing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2ard.asp), up through the C2 level. Unfortunately there isn't a "cookbook" approach for doing it, you really need to understand what you are doing, and the consequences and benefits of each decision.

-PatP|||I found out . Thanks for reply. C2 auditing however is not needed . I found out that from SQL Profiler , you can script the whole trace as SQL and execute it in ISQLW . This gives back a Trace ID . Next you have to run sp_trace_Setstatus @.traceID, 1 to run it .

Thanks for responding . I appreciate that

any user access audit programs available?

Hello all, does anyone know of a SS2005RS user audit program that an administrator can run on a RS server to show which userids have access to folders? I have in mind a pgm that would show:

folder users

Home user01, user02, user03

folderA user01,user02, user05

folderB user02, user06

Is there a pgm available as a download, or does someone have a home-grown pgm whose source they would let out?

Has anyone else faced this need?

Thanks in advance

I don't know of such a program but writing your own shouldn't be that difficult. You can call ReportingService2005.GetPolicies method to obtain the secuity polices per folder.|||Mr. Lachev, thanks so much, I'll do that. P.S. I like your website.

Any type of Audit mechaninsm in SQL Server?

Does anyone know if there is any type of audit trail mechanism used in SQl Server 2000 or 20005?

In our production environment, some of our stored procedures, views and data in our tables has changed. It would be nice to have a footprint of what was changed by whom and one.

For our code we use source safe but for SQL Server I am not aware of any type of auditing mechanism.

AFAIK, there is no built in or an add-on from MS for auditing proc or schema changes in SQL Server. Either you need to manually control the change process (via custom app or VSS) or use some third party tools.

Any possible optimization for the following SQL statement

Hi all,
I am relatively new to this group and forgive me if this question is
too trivial...
Say, I have an audit table AccountAudit that contains account records.
When each batch of rows are inserted, the timestamp is saved at
UpdateTime column. At a certain timestamp, there are multiple rows for
an account.
Now, given an Account# and ActionType, I'd like to get the 1st and 2rd
most recent groups of rows. Here is my SQL statement. I wonder if there
is any way to optimize it.
select * from AccountAudit where Account = 4 and ActionType = 'C'
and UpdatedTime in
(select distinct top 2 UpdatedTime from AccountAudit
where Account = 4 and ActionType = 'C'
order by UpdatedTime desc)
Thanks in advance.
FrankYou could also do this in a single SELECT instead of the nested query.
SELECT DISTINCT TOP 2 Account, ActionType, UpdatedTime
FROM Accounts
WHERE Account = 4 and ActionType = 'C'
ORDER BY UpdatedTime DESC|||To add to Lubdha's response, it is also very important to consider indexing.
A composite index on Account, ActionType, UpdatedTime may help optimize this
particular query. You'll need to consider your overall workload in order to
determine the best indexing strategy.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<fzhang@.calamos.com> wrote in message
news:1149881438.607379.14290@.i39g2000cwa.googlegroups.com...
> Hi all,
> I am relatively new to this group and forgive me if this question is
> too trivial...
> Say, I have an audit table AccountAudit that contains account records.
> When each batch of rows are inserted, the timestamp is saved at
> UpdateTime column. At a certain timestamp, there are multiple rows for
> an account.
> Now, given an Account# and ActionType, I'd like to get the 1st and 2rd
> most recent groups of rows. Here is my SQL statement. I wonder if there
> is any way to optimize it.
> select * from AccountAudit where Account = 4 and ActionType = 'C'
> and UpdatedTime in
> (select distinct top 2 UpdatedTime from AccountAudit
> where Account = 4 and ActionType = 'C'
> order by UpdatedTime desc)
>
> Thanks in advance.
> Frank
>

Any possible optimization for the following SQL statement

Hi all,
I am relatively new to this group and forgive me if this question is
too trivial...
Say, I have an audit table AccountAudit that contains account records.
When each batch of rows are inserted, the timestamp is saved at
UpdateTime column. At a certain timestamp, there are multiple rows for
an account.
Now, given an Account# and ActionType, I'd like to get the 1st and 2rd
most recent groups of rows. Here is my SQL statement. I wonder if there
is any way to optimize it.
select * from AccountAudit where Account = 4 and ActionType = 'C'
and UpdatedTime in
(select distinct top 2 UpdatedTime from AccountAudit
where Account = 4 and ActionType = 'C'
order by UpdatedTime desc)
Thanks in advance.
FrankYou could also do this in a single SELECT instead of the nested query.
SELECT DISTINCT TOP 2 Account, ActionType, UpdatedTime
FROM Accounts
WHERE Account = 4 and ActionType = 'C'
ORDER BY UpdatedTime DESC|||To add to Lubdha's response, it is also very important to consider indexing.
A composite index on Account, ActionType, UpdatedTime may help optimize this
particular query. You'll need to consider your overall workload in order to
determine the best indexing strategy.
Hope this helps.
Dan Guzman
SQL Server MVP
<fzhang@.calamos.com> wrote in message
news:1149881438.607379.14290@.i39g2000cwa.googlegroups.com...
> Hi all,
> I am relatively new to this group and forgive me if this question is
> too trivial...
> Say, I have an audit table AccountAudit that contains account records.
> When each batch of rows are inserted, the timestamp is saved at
> UpdateTime column. At a certain timestamp, there are multiple rows for
> an account.
> Now, given an Account# and ActionType, I'd like to get the 1st and 2rd
> most recent groups of rows. Here is my SQL statement. I wonder if there
> is any way to optimize it.
> select * from AccountAudit where Account = 4 and ActionType = 'C'
> and UpdatedTime in
> (select distinct top 2 UpdatedTime from AccountAudit
> where Account = 4 and ActionType = 'C'
> order by UpdatedTime desc)
>
> Thanks in advance.
> Frank
>