Authentication

Let's say we've got 2 computers - [A] and [B] where is a SQL Server.
Is it possible to make user from [A] log into instance of SQL Server on
[B] with Windows Authentication? So is there a possibility to create a
user on [B] that will correspond exactly to [A], or not?
[A] and [B] could be totally independent (not in local network)
ThanksHi
If there is no trust relationship between the two domains (computers) then
you can not use Windows Authentication.
John
"christof" <nomail@.nomail.de> wrote in message
news:eQO7$cBUGHA.4492@.TK2MSFTNGP09.phx.gbl...
> Let's say we've got 2 computers - [A] and [B] where is a SQL Server.
> Is it possible to make user from [A] log into instance of SQL Server on
> [B] with Windows Authentication? So is there a possibility to create a
> user on [B] that will correspond exactly to [A], or not?
> [A] and [B] could be totally independent (not in local network)
> Thanks|||I have two trusted domain in the same forest, one Windows 2000 server(domain
A), and the other one is windows 2003 server(domain B), trust relationship
bet two domain seems working, I can access folder from both domain,
in domain B, I have a member server with windows 2003 server, an
application with SQL DB installed. application is web base application,
I logon to domain A, launch the application in domain B, using URL, next I
get a Enter network Password, with username, password, domain, I have to
logon to Domain B in order to access the application in Domain B.
My understanding of trust relationship between domain is I do not need to
logon to domain B again because is already trusted.
Please help me.
TQ
wcj
"John Bell" wrote:

> Hi
> If there is no trust relationship between the two domains (computers) then
> you can not use Windows Authentication.
> John
> "christof" <nomail@.nomail.de> wrote in message
> news:eQO7$cBUGHA.4492@.TK2MSFTNGP09.phx.gbl...
>
>|||Hi
If you have to login to the second domain then the trust relationship is
probably not correct. It may be that domain A trusts domain B but you not
vice versa.
John
"bk" wrote:
> I have two trusted domain in the same forest, one Windows 2000 server(doma
in
> A), and the other one is windows 2003 server(domain B), trust relationship
> bet two domain seems working, I can access folder from both domain,
> in domain B, I have a member server with windows 2003 server, an
> application with SQL DB installed. application is web base application,
> I logon to domain A, launch the application in domain B, using URL, next I
> get a Enter network Password, with username, password, domain, I have to
> logon to Domain B in order to access the application in Domain B.
> My understanding of trust relationship between domain is I do not need to
> logon to domain B again because is already trusted.
> Please help me.
> TQ
> wcj
> "John Bell" wrote:
>

Auditing SQL Server 2005 through transaction log

Hello,

We are maintaining an internal ASP.NET v2.0 website which is quite big and already in production. The underlying SQL Server 2005 database contains 350+ tables.

Recently, we have been asked to implement a new feature which seems functionally quite simple. We have to track every single data modification, which includes insertions, deletions and modifications. This information should be presented to power users in the form of readable strings right in an admin section of our website.

Our team of architects is working on a way to make it possible without putting the SQL Server to a crawl. One thing is for sure, SQL Server 2005 already does the job through its transaction log. It should be a good idea to use it directly instead of managing our own log based on triggers. Why put more pressure on the server to write data that is already logged by the database engine? We have heard that Microsoft's SQL Server team do not support this concept and are wondering why...

It's quite easy to find queries on the web that output very useful information such as date of transactions and what they have done. Although, the data involved in those transactions seems to be stored in a binary field which can be retrived using this query: SELECT "log record" FROM ::fn_dblog(null,null)

3rd parties such as Apex SQL are already doing a great job at decrypting it for us. This is very useful but not efficient since those tools do a very generic job. We would like to optimize it for our needs. All we need to know is who made the modifications, when, in which tables and what are the new values.

We believe that we would have to decrypt the "log record" field from the ::fn_dblog(null, null) table. Is there any way to get basic documentation about how to do it?

Thanks!

Marc Lacoursiere

RooSoft Computing

The format of the transaction log is undocumented, as it will change from release to release.

Thanks,|||

Hi,

I'm working with Marc on that point and I would like to know if there is or if Microsoft expect to expose an interface that can allow us to read the transaction log. What are the plans for futur releases.

Thanks

|||The transaction log contains physical information that are often just blocks of bytes which are meaningless in terms of the DDL or DML that instigated them. The log was never intended for audit purposes and really should not be used that way.|||

Thanks for your Post Peter,

I understand that the transaction log purpose is for recovery only but you must admit that it could be a great way to audit changes without adding any overhead to the server.

What we would like to do is taking a transaction log backup on a regulary basis and read those files as they cumulated in the directory. This will allow us to log any DML changes into a Log table located on another server.

I also understand that the online transaction log format can change from one version to another but correct me if I'm wrong, the transaction log backup is compatible between SQL 2000 and SQL 2005 that means that the format of a .trn file should remain compatible as SQL Server evoluate.

thanks for your feedback|||

Adding the information needed to use the log for auditing would add significant overhead which is one of the reasons it has not been done.

You might look at the Change Data Capture functionality in the upcoming SQL Server 2008 release. http://connect.microsoft.com/sqlserver/ has a CTP preview release available.

|||

Our guess as of now is that some timely transaction log backup analysis on a remote machine could help keep our database server usage to an acceptable level as it already handles loads of data.

We doubt that using triggers on each table would be much more effective. This would certainly slow down every transaction. As our database grows, we must optimize DML operations to keep the website running fast.

|||

>> SELECT "log record" FROM ::fn_dblog(null,null)

>> All we need to know is who made the modifications, when, in which tables and what are the new values.

"Who" is not available on a log record by log record basis. It is not part of that binary data in most cases. There are some few records that contain a "who" such as BEGIN TRAN log records. This is included in other output columns of fn_dblog().

When is not available on a log record by log record basis. There are some few record types that contain a "when" such as BEGIN TRAN and END TRAN log records. These are included as other columns in fn_dblog().

Which table is not actually stored in the log record in SQL 2005 and later. This is due to partitioning. The partition has to be linked through the catalog metadata back to its base table and index. This is attempted by fn_dblog() itself and shows up as other columns in the output. DDL can make this lookup fail.

"New values" are often just a byte by byte binary diff of the old row from the new row, not the full values themselves. The log record code does not actually know how to crack the row binary data, it just passes it along to other components in the system. i.e., if you update a INT column from a value of 1 to a value of 257, we may only log a single byte 0x01 as the difference.

The SQL 2005 output for fn_dblog() has been supplemented to try to make some of this easier, but the reality is that some of what you want is just not in the log in many cases.

|||

Thanks Peter for this interesting complement of information.

We actually take care of the "When" and the "Who" because each table of our database contain an updatedBy and updatedDt column. These columns are systematicly updated on each changes by the application layer. We only need to find the new values of the modification.

For the byte by byte binary diff, I guess that you are talking about the online transaction log because taking a closer look at the transaction log backup file (.trn) indicates that new values are stored in the file. I've opened it with an hex editor and I was able to see the new inserted values.

|||

You could look at AuditDB of Lumigent, can be customized on details what you demand.

With best regards.

Jan H. Kanon

Auditing SQL Server 2005 through transaction log

Hello,

We are maintaining an internal ASP.NET v2.0 website which is quite big and already in production. The underlying SQL Server 2005 database contains 350+ tables.

Recently, we have been asked to implement a new feature which seems functionally quite simple. We have to track every single data modification, which includes insertions, deletions and modifications. This information should be presented to power users in the form of readable strings right in an admin section of our website.

Our team of architects is working on a way to make it possible without putting the SQL Server to a crawl. One thing is for sure, SQL Server 2005 already does the job through its transaction log. It should be a good idea to use it directly instead of managing our own log based on triggers. Why put more pressure on the server to write data that is already logged by the database engine? We have heard that Microsoft's SQL Server team do not support this concept and are wondering why...

It's quite easy to find queries on the web that output very useful information such as date of transactions and what they have done. Although, the data involved in those transactions seems to be stored in a binary field which can be retrived using this query: SELECT "log record" FROM ::fn_dblog(null,null)

3rd parties such as Apex SQL are already doing a great job at decrypting it for us. This is very useful but not efficient since those tools do a very generic job. We would like to optimize it for our needs. All we need to know is who made the modifications, when, in which tables and what are the new values.

We believe that we would have to decrypt the "log record" field from the ::fn_dblog(null, null) table. Is there any way to get basic documentation about how to do it?

Thanks!

Marc Lacoursiere

RooSoft Computing

The format of the transaction log is undocumented, as it will change from release to release.

Thanks,|||

Hi,

I'm working with Marc on that point and I would like to know if there is or if Microsoft expect to expose an interface that can allow us to read the transaction log. What are the plans for futur releases.

Thanks

|||The transaction log contains physical information that are often just blocks of bytes which are meaningless in terms of the DDL or DML that instigated them. The log was never intended for audit purposes and really should not be used that way.|||

Thanks for your Post Peter,

I understand that the transaction log purpose is for recovery only but you must admit that it could be a great way to audit changes without adding any overhead to the server.

What we would like to do is taking a transaction log backup on a regulary basis and read those files as they cumulated in the directory. This will allow us to log any DML changes into a Log table located on another server.

I also understand that the online transaction log format can change from one version to another but correct me if I'm wrong, the transaction log backup is compatible between SQL 2000 and SQL 2005 that means that the format of a .trn file should remain compatible as SQL Server evoluate.

thanks for your feedback|||

Adding the information needed to use the log for auditing would add significant overhead which is one of the reasons it has not been done.

You might look at the Change Data Capture functionality in the upcoming SQL Server 2008 release. http://connect.microsoft.com/sqlserver/ has a CTP preview release available.

|||

Our guess as of now is that some timely transaction log backup analysis on a remote machine could help keep our database server usage to an acceptable level as it already handles loads of data.

We doubt that using triggers on each table would be much more effective. This would certainly slow down every transaction. As our database grows, we must optimize DML operations to keep the website running fast.

|||

>> SELECT "log record" FROM ::fn_dblog(null,null)

>> All we need to know is who made the modifications, when, in which tables and what are the new values.

"Who" is not available on a log record by log record basis. It is not part of that binary data in most cases. There are some few records that contain a "who" such as BEGIN TRAN log records. This is included in other output columns of fn_dblog().

When is not available on a log record by log record basis. There are some few record types that contain a "when" such as BEGIN TRAN and END TRAN log records. These are included as other columns in fn_dblog().

Which table is not actually stored in the log record in SQL 2005 and later. This is due to partitioning. The partition has to be linked through the catalog metadata back to its base table and index. This is attempted by fn_dblog() itself and shows up as other columns in the output. DDL can make this lookup fail.

"New values" are often just a byte by byte binary diff of the old row from the new row, not the full values themselves. The log record code does not actually know how to crack the row binary data, it just passes it along to other components in the system. i.e., if you update a INT column from a value of 1 to a value of 257, we may only log a single byte 0x01 as the difference.

The SQL 2005 output for fn_dblog() has been supplemented to try to make some of this easier, but the reality is that some of what you want is just not in the log in many cases.

|||

Thanks Peter for this interesting complement of information.

We actually take care of the "When" and the "Who" because each table of our database contain an updatedBy and updatedDt column. These columns are systematicly updated on each changes by the application layer. We only need to find the new values of the modification.

For the byte by byte binary diff, I guess that you are talking about the online transaction log because taking a closer look at the transaction log backup file (.trn) indicates that new values are stored in the file. I've opened it with an hex editor and I was able to see the new inserted values.

|||

You could look at AuditDB of Lumigent, can be customized on details what you demand.

With best regards.

Jan H. Kanon

Auditing SQL Server 2005 through transaction log

Hello,

We are maintaining an internal ASP.NET v2.0 website which is quite big and already in production. The underlying SQL Server 2005 database contains 350+ tables.

Recently, we have been asked to implement a new feature which seems functionally quite simple. We have to track every single data modification, which includes insertions, deletions and modifications. This information should be presented to power users in the form of readable strings right in an admin section of our website.

Our team of architects is working on a way to make it possible without putting the SQL Server to a crawl. One thing is for sure, SQL Server 2005 already does the job through its transaction log. It should be a good idea to use it directly instead of managing our own log based on triggers. Why put more pressure on the server to write data that is already logged by the database engine? We have heard that Microsoft's SQL Server team do not support this concept and are wondering why...

It's quite easy to find queries on the web that output very useful information such as date of transactions and what they have done. Although, the data involved in those transactions seems to be stored in a binary field which can be retrived using this query: SELECT "log record" FROM ::fn_dblog(null,null)

3rd parties such as Apex SQL are already doing a great job at decrypting it for us. This is very useful but not efficient since those tools do a very generic job. We would like to optimize it for our needs. All we need to know is who made the modifications, when, in which tables and what are the new values.

We believe that we would have to decrypt the "log record" field from the ::fn_dblog(null, null) table. Is there any way to get basic documentation about how to do it?

Thanks!

Marc Lacoursiere

RooSoft Computing

The format of the transaction log is undocumented, as it will change from release to release.

Thanks,|||

Hi,

I'm working with Marc on that point and I would like to know if there is or if Microsoft expect to expose an interface that can allow us to read the transaction log. What are the plans for futur releases.

Thanks

|||The transaction log contains physical information that are often just blocks of bytes which are meaningless in terms of the DDL or DML that instigated them. The log was never intended for audit purposes and really should not be used that way.|||

Thanks for your Post Peter,

I understand that the transaction log purpose is for recovery only but you must admit that it could be a great way to audit changes without adding any overhead to the server.

What we would like to do is taking a transaction log backup on a regulary basis and read those files as they cumulated in the directory. This will allow us to log any DML changes into a Log table located on another server.

I also understand that the online transaction log format can change from one version to another but correct me if I'm wrong, the transaction log backup is compatible between SQL 2000 and SQL 2005 that means that the format of a .trn file should remain compatible as SQL Server evoluate.

thanks for your feedback|||

Adding the information needed to use the log for auditing would add significant overhead which is one of the reasons it has not been done.

You might look at the Change Data Capture functionality in the upcoming SQL Server 2008 release. http://connect.microsoft.com/sqlserver/ has a CTP preview release available.

|||

Our guess as of now is that some timely transaction log backup analysis on a remote machine could help keep our database server usage to an acceptable level as it already handles loads of data.

We doubt that using triggers on each table would be much more effective. This would certainly slow down every transaction. As our database grows, we must optimize DML operations to keep the website running fast.

|||

>> SELECT "log record" FROM ::fn_dblog(null,null)

>> All we need to know is who made the modifications, when, in which tables and what are the new values.

"Who" is not available on a log record by log record basis. It is not part of that binary data in most cases. There are some few records that contain a "who" such as BEGIN TRAN log records. This is included in other output columns of fn_dblog().

When is not available on a log record by log record basis. There are some few record types that contain a "when" such as BEGIN TRAN and END TRAN log records. These are included as other columns in fn_dblog().

Which table is not actually stored in the log record in SQL 2005 and later. This is due to partitioning. The partition has to be linked through the catalog metadata back to its base table and index. This is attempted by fn_dblog() itself and shows up as other columns in the output. DDL can make this lookup fail.

"New values" are often just a byte by byte binary diff of the old row from the new row, not the full values themselves. The log record code does not actually know how to crack the row binary data, it just passes it along to other components in the system. i.e., if you update a INT column from a value of 1 to a value of 257, we may only log a single byte 0x01 as the difference.

The SQL 2005 output for fn_dblog() has been supplemented to try to make some of this easier, but the reality is that some of what you want is just not in the log in many cases.

|||

Thanks Peter for this interesting complement of information.

We actually take care of the "When" and the "Who" because each table of our database contain an updatedBy and updatedDt column. These columns are systematicly updated on each changes by the application layer. We only need to find the new values of the modification.

For the byte by byte binary diff, I guess that you are talking about the online transaction log because taking a closer look at the transaction log backup file (.trn) indicates that new values are stored in the file. I've opened it with an hex editor and I was able to see the new inserted values.

Auditing SP Execute.

To meet audit requirements, I have to maintain an audit log of when a
particular sp was execute and by whom. I am not willing to use the c2
option for just one stored procedure. There are 2 users who can run
the stored procedure from enterprise manager or query analyser (others
run it from an app). Any suggetions?
Thank You,
-pranayYou can run a trace that is filtered only on this sp. That is the easiest
and least expensive way. Or you would have to buy one of the 3rd party log
tools to view the contents of the log.
Andrew J. Kelly SQL MVP
"Pranay Pandya" <ppandya@.gmail.com> wrote in message
news:1109178725.456740.135850@.z14g2000cwz.googlegroups.com...
> To meet audit requirements, I have to maintain an audit log of when a
> particular sp was execute and by whom. I am not willing to use the c2
> option for just one stored procedure. There are 2 users who can run
> the stored procedure from enterprise manager or query analyser (others
> run it from an app). Any suggetions?
> Thank You,
> -pranay
>|||I want to capture the user name and the time (using sql). i dont want
to run profiler for ever. When someone runs the sp may be i can get
the userid and insert it into a table.|||Without monitoring the activity with either trace or a 3rd party tool there
is no way to do this in Sql2000.
Andrew J. Kelly SQL MVP
"Pranay Pandya" <ppandya@.gmail.com> wrote in message
news:1109187761.063534.20330@.z14g2000cwz.googlegroups.com...
>I want to capture the user name and the time (using sql). i dont want
> to run profiler for ever. When someone runs the sp may be i can get
> the userid and insert it into a table.
>|||As long as the users only have execute permissions on the proc i.e. they
can't change it, then just add some code at the start of the proc to log to
a table passing getdate() and suser_sname() to capture the time and user
executing the proc. This assumes that the app doesn't use a single service
account (in which case you won't be able to get the user name without
modifying the app to stuff it into context_info)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Pranay Pandya" <ppandya@.gmail.com> wrote in message
news:1109178725.456740.135850@.z14g2000cwz.googlegroups.com...
> To meet audit requirements, I have to maintain an audit log of when a
> particular sp was execute and by whom. I am not willing to use the c2
> option for just one stored procedure. There are 2 users who can run
> the stored procedure from enterprise manager or query analyser (others
> run it from an app). Any suggetions?
> Thank You,
> -pranay
>|||Andrew J. Kelly wrote:
> Without monitoring the activity with either trace or a 3rd party tool ther
e
> is no way to do this in Sql2000.
>
Of course he can.
create table AuditLog (WhenItRan datetime, WhoRanIt sysname)
go
Add to beginning of SP:
insert into AuditLog values (getdate(), suser_sname())|||Ahh yes. My original reading was he was looking to trace the sp_executesql
sp but I think I was mistaken. If it is a user sp then sure.
Andrew J. Kelly SQL MVP
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OcM6uPfGFHA.3076@.tk2msftngp13.phx.gbl...
> As long as the users only have execute permissions on the proc i.e. they
> can't change it, then just add some code at the start of the proc to log
> to a table passing getdate() and suser_sname() to capture the time and
> user executing the proc. This assumes that the app doesn't use a single
> service account (in which case you won't be able to get the user name
> without modifying the app to stuff it into context_info)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Pranay Pandya" <ppandya@.gmail.com> wrote in message
> news:1109178725.456740.135850@.z14g2000cwz.googlegroups.com...
>|||Thank you Every for the posts. I am going to create the audit table and
log it.
Andrew J. Kelly wrote:
> Ahh yes. My original reading was he was looking to trace the
sp_executesql[vbcol=seagreen]
> sp but I think I was mistaken. If it is a user sp then sure.
> --
> Andrew J. Kelly SQL MVP
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OcM6uPfGFHA.3076@.tk2msftngp13.phx.gbl...
they[vbcol=seagreen]
to log[vbcol=seagreen]
and[vbcol=seagreen]
single[vbcol=seagreen]
name[vbcol=seagreen]
when a[vbcol=seagreen]
the c2[vbcol=seagreen]
run[vbcol=seagreen]
(others[vbcol=seagreen]

Auditing permission changes

What I would like is to log all the permission changes into a table.
Like if a developer changed a permission to a view or a table or any
other object it would get logged into a table. Also logging database
and server level permission changes like some one giving some one a
dbowner access. Any suggessions?
Thank You,
-Pranay
> What I would like is to log all the permission changes into a table.
> Like if a developer changed a permission to a view or a table or any
> other object it would get logged into a table. Also logging database
> and server level permission changes like some one giving some one a
> dbowner access. Any suggessions?
Check the SQL Profiler in Books OnLine, specifically "Security Audit Event
Classes" topic.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Hi Dejan,
Now I am running a trace. But the trace captures events like temp
index creations. I tried not like 'Create Index #' in the text data
but that filter did not work. It is still capturing temp index
creations. I filtered out temp db so the temp table creations are not
captured now. Any suggessions on removing temp index creations?
|||> Now I am running a trace. But the trace captures events like temp
> index creations. I tried not like 'Create Index #' in the text data
> but that filter did not work. It is still capturing temp index
> creations. I filtered out temp db so the temp table creations are not
> captured now. Any suggessions on removing temp index creations?
Use % wildchar, not # - 'Create Index%' should work.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Auditing

From my reading of the online book it appears SQL can log all changes made
to each database, how do you switch this on and where is the file. What I am
trying to achieve is to build a monitoring process to identify what changes
a user performed for each session.
Thanks in advance.SQL Server does not have this ability. The log is to ensure database
integrity and not for auditing purposes. You have several choices to audit
user actions. One is to create triggers on each table that you wish to
monitor and write code to store the information into audit tables of your
design. Or you can use a 3rd party product such as Entegra from
www.lumigent.com.
Andrew J. Kelly
SQL Server MVP
"Con" <conaus@.hotmail.com> wrote in message
news:Ow409b56DHA.2568@.TK2MSFTNGP10.phx.gbl...

quote:

---

> From my reading of the online book it appears SQL can log all changes made
> to each database, how do you switch this on and where is the file. What I

---

am

quote:

---

> trying to achieve is to build a monitoring process to identify what

---

changes

quote:

---

> a user performed for each session.
> Thanks in advance.
>

---

audit trail...

Hi...
A much lamented question, I guess..

I'm trying to create a simple audit trail.
log the changes to an SQL 2000 table, so that they are written into a
mirror table. The entire record, only the updated one, i.e. if say
only one field changes, the audit table will be inserted with one
record that has one field changed. if the record has been deleted, it
still will be written.
I'm not worrying about additional fields to the audit table containing
descriptive flags of what action took place yet. I just want the
mirror image for starters.

I got the script of the 'create table' off Query analyzer. created the
audit table.
the trigger looks like this:

CREATE TRIGGER dt_tbl1_audit
on tbl1
for insert, update, delete

AS
insert into tbl1_audit
select * from inserted

the table has about 50 fields or so, so I tried to make do with *'s.
didn't work, so I tried copying and pasting the explicit list of field
names
instead (though I'm not sure why it needs that if the two tables are
identically structured).

in either case, if I update any field on the audited table, I get this
error:
(after getting the warning that the results may take a long time to
process etc, the original table has over 100,000 rows)

"another user has modified the contents of this table or view,
the database row you are modifying no longer exists in the database
database error: insert error:
column name or number of supplied values does not match table
definition"

I'm not sure what's wrong, the two tables are identical (I copy pasted
the create table script with no changes). no other users except me on
this database.
i've removed all constraints and indexes from the audit table.

thanksHi

It seems that you are having problems with posting!!!

If speed is important you should think about keeping simple copies of the
inserted and deleted tables (possibly with other columns such as a timestamp
and user name) as this will mean that the trigger the least processing and
not causing you transactions to be open for a elongated period. The work of
resolving what columns have been updated can then be done either when
reporting or at a more convenient time.

If you wish to do this processing withing the trigger check out Books online
under the "Create Trigger" topic, you can find information about using the
UPDATED() and COLUMNS_UPDATED() functions and example of how to use them.
Another alternative method is to use a log file reading product such as
those from Lumigent (Lumigent Log Explorer) http://www.lumigent.com/ or PI,
http://www.logpi.com
and process the information in the log files.

HTH

John

"Me" <heruti@.lycos.com> wrote in message
news:2d4c3262.0411261512.6d8072aa@.posting.google.c om...
> Hi...
> A much lamented question, I guess..
> I'm trying to create a simple audit trail.
> log the changes to an SQL 2000 table, so that they are written into a
> mirror table. The entire record, only the updated one, i.e. if say
> only one field changes, the audit table will be inserted with one
> record that has one field changed. if the record has been deleted, it
> still will be written.
> I'm not worrying about additional fields to the audit table containing
> descriptive flags of what action took place yet. I just want the
> mirror image for starters.
> I got the script of the 'create table' off Query analyzer. created the
> audit table.
> the trigger looks like this:
> CREATE TRIGGER dt_tbl1_audit
> on tbl1
> for insert, update, delete
> AS
> insert into tbl1_audit
> select * from inserted
>
> the table has about 50 fields or so, so I tried to make do with *'s.
> didn't work, so I tried copying and pasting the explicit list of field
> names
> instead (though I'm not sure why it needs that if the two tables are
> identically structured).
> in either case, if I update any field on the audited table, I get this
> error:
> (after getting the warning that the results may take a long time to
> process etc, the original table has over 100,000 rows)
> "another user has modified the contents of this table or view,
> the database row you are modifying no longer exists in the database
> database error: insert error:
> column name or number of supplied values does not match table
> definition"
> I'm not sure what's wrong, the two tables are identical (I copy pasted
> the create table script with no changes). no other users except me on
> this database.
> i've removed all constraints and indexes from the audit table.
>
> thanks|||Me (heruti@.lycos.com) writes:
> I got the script of the 'create table' off Query analyzer. created the
> audit table.
> the trigger looks like this:
> CREATE TRIGGER dt_tbl1_audit
> on tbl1
> for insert, update, delete
> AS
> insert into tbl1_audit
> select * from inserted
>
> the table has about 50 fields or so, so I tried to make do with *'s.
> didn't work, so I tried copying and pasting the explicit list of field
> names
> instead (though I'm not sure why it needs that if the two tables are
> identically structured).

Depends on what columns there are in the tables. If you have an IDENTITY
colunm in the source table, the corresponding table in the audit table
cannot have the IDENTITY property. And if there are timestamp columns,
you would have to make them binary(8) in the target table.

In any case, some sort of a primary key for the target table would be a good
idea.

> in either case, if I update any field on the audited table, I get this
> error:
> (after getting the warning that the results may take a long time to
> process etc, the original table has over 100,000 rows)

So where does this warning come from?

> "another user has modified the contents of this table or view,
> the database row you are modifying no longer exists in the database
> database error: insert error:
> column name or number of supplied values does not match table
> definition"
> I'm not sure what's wrong, the two tables are identical (I copy pasted
> the create table script with no changes). no other users except me on
> this database.
> i've removed all constraints and indexes from the audit table.

Well, we don't even know the definition of the tables, so how could we
tell what is going on?

Do you get this error when you perform an update from Query Analyzer? If
so, can you cut and paste the complete error message? The error message
should include procedure name and line number where the message appears.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||yes, I did have trouble posting, I do apologize..

IE6 reported some sort of 404 error when I clicked submit in the
dejanews post. I assumed it didn't & tried several dozen times until I
accidentally discovered that it did post (dejanews says it takes
several hours to post, so traditionally I wouldn't have discovered
this, but this time I stumbled on another site, sort of a gateway to
google groups, which showed my post immediately... seems useful.
http://news-reader.org/comp.databases.ms-sqlserver/

re the audit, I did get the code to work both ways, with *'s notation
and also with detail listing of all the fields. so this post is
generally for the benefit of other befuddled customers on my trail..

The following trigger works. audits updates/inserts on the table
tblSource, into the log table tblSource_Audit which has the same
structure plus the two fields 'LogActionType' (varchar 10) and
'LogDate':

CREATE TRIGGER dt_insupd
on tblSource
for Insert,Update AS
INSERT INTO tblSource_audit
select 'insert/upd',GetDate(),
* from Inserted ins
GO

Still, I thought it would be safer (future maintenance wise, so the
trigger won't break if fields are added to the source table)
to convert the whole thing into detailed column notation (too long to
list here), and Later add a little condition code to it that would
post 'insert' and 'update' strings identifying the two operations and
not the combined string above:

CREATE TRIGGER dt_insupd
on tblSource
for Insert,Update AS

Declare @.ActionType VARCHAR(10)
Declare @.DeleteCnt int
set @.DeleteCnt = (select count(*) from deleted)
if @.DeleteCnt = 0
begin
set @.ActionType = 'Insert'
end
ELSE
set @.ActionType = 'Update'

INSERT INTO tblSource_audit
select @.ActionType,GetDate(),
* from Inserted ins
GO

Thanks everyone for your help. Any more comments, of course, welcome.

Audit Logs in SQL SERVER ?

Hi,
Does SQL provide an Audit log on who is accessing which database and what's
being done on each table that's kept in certain system tables or is there an
y
stored procedures to keep track of this ?
tks & rdgs
maxzsimHi maxzsim
I think SQL Profiler can help you in achieving this task.
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:

> Hi,
> Does SQL provide an Audit log on who is accessing which database and what
's
> being done on each table that's kept in certain system tables or is there
any
> stored procedures to keep track of this ?
> tks & rdgs
> maxzsim|||Hi Chandra,
is there any alternative besides the SQL Profiler ?
rdgs
"Chandra" wrote:
[vbcol=seagreen]
> Hi maxzsim
> I think SQL Profiler can help you in achieving this task.
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> ---
>
> "maxzsim" wrote:
>|||Hi maxzsim
You can use NT EventLog for this, but it will not give you all the
information that you require. I prefer using SQL Profiler for this purpose.
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:
[vbcol=seagreen]
> Hi Chandra,
> is there any alternative besides the SQL Profiler ?
> rdgs
> "Chandra" wrote:
>|||Hi,
I recommend you to enable server side tracing if the server is Production.
This will use the resouce very less compared to SQL Profiler.
Have a look into the below great article from Vyas on server side tracing
with examples to configure:-
http://vyaskn.tripod.com/server_sid..._sql_server.htm
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:6485BF63-1708-4D96-B245-E056D12EEEAD@.microsoft.com...
> Hi,
> Does SQL provide an Audit log on who is accessing which database and
what's
> being done on each table that's kept in certain system tables or is there
any
> stored procedures to keep track of this ?
> tks & rdgs
> maxzsim|||An alternative might be to look at many of the transaction log analyzers out
there:
http://www.apexsql.com/sql_tools_log.htm
http://www.lumigent.com/products/entegra.html
http://www.logpi.com/
to name but three.
"maxzsim" wrote:

> Hi,
> Does SQL provide an Audit log on who is accessing which database and what
's
> being done on each table that's kept in certain system tables or is there
any
> stored procedures to keep track of this ?
> tks & rdgs
> maxzsim|||It might be worth mentioning that SELECT cannot be obtained from the transac
tion log. So the tools
that can audit reads do this using a background profiler trace...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scrat" <Scrat@.discussions.microsoft.com> wrote in message
news:19322556-D9C2-467F-ABBA-BC1C8845E222@.microsoft.com...[vbcol=seagreen]
> An alternative might be to look at many of the transaction log analyzers o
ut
> there:
> http://www.apexsql.com/sql_tools_log.htm
> http://www.lumigent.com/products/entegra.html
> http://www.logpi.com/
> to name but three.
> "maxzsim" wrote:
>

Audit Logs in SQL SERVER ?

Hi,
Does SQL provide an Audit log on who is accessing which database and what's
being done on each table that's kept in certain system tables or is there any
stored procedures to keep track of this ?
tks & rdgs
maxzsim
Hi maxzsim
I think SQL Profiler can help you in achieving this task.
best Regards,
Chandra
http://chanduas.blogspot.com/
"maxzsim" wrote:

> Hi,
> Does SQL provide an Audit log on who is accessing which database and what's
> being done on each table that's kept in certain system tables or is there any
> stored procedures to keep track of this ?
> tks & rdgs
> maxzsim
|||Hi Chandra,
is there any alternative besides the SQL Profiler ?
rdgs
"Chandra" wrote:
[vbcol=seagreen]
> Hi maxzsim
> I think SQL Profiler can help you in achieving this task.
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
>
> "maxzsim" wrote:
|||Hi maxzsim
You can use NT EventLog for this, but it will not give you all the
information that you require. I prefer using SQL Profiler for this purpose.
best Regards,
Chandra
http://chanduas.blogspot.com/
"maxzsim" wrote:
[vbcol=seagreen]
> Hi Chandra,
> is there any alternative besides the SQL Profiler ?
> rdgs
> "Chandra" wrote:
|||Hi,
I recommend you to enable server side tracing if the server is Production.
This will use the resouce very less compared to SQL Profiler.
Have a look into the below great article from Vyas on server side tracing
with examples to configure:-
http://vyaskn.tripod.com/server_side...sql_server.htm
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:6485BF63-1708-4D96-B245-E056D12EEEAD@.microsoft.com...
> Hi,
> Does SQL provide an Audit log on who is accessing which database and
what's
> being done on each table that's kept in certain system tables or is there
any
> stored procedures to keep track of this ?
> tks & rdgs
> maxzsim
|||An alternative might be to look at many of the transaction log analyzers out
there:
http://www.apexsql.com/sql_tools_log.htm
http://www.lumigent.com/products/entegra.html
http://www.logpi.com/
to name but three.
"maxzsim" wrote:

> Hi,
> Does SQL provide an Audit log on who is accessing which database and what's
> being done on each table that's kept in certain system tables or is there any
> stored procedures to keep track of this ?
> tks & rdgs
> maxzsim
|||It might be worth mentioning that SELECT cannot be obtained from the transaction log. So the tools
that can audit reads do this using a background profiler trace...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scrat" <Scrat@.discussions.microsoft.com> wrote in message
news:19322556-D9C2-467F-ABBA-BC1C8845E222@.microsoft.com...[vbcol=seagreen]
> An alternative might be to look at many of the transaction log analyzers out
> there:
> http://www.apexsql.com/sql_tools_log.htm
> http://www.lumigent.com/products/entegra.html
> http://www.logpi.com/
> to name but three.
> "maxzsim" wrote:

Audit Logs in SQL SERVER ?

Hi,
Does SQL provide an Audit log on who is accessing which database and what's
being done on each table that's kept in certain system tables or is there any
stored procedures to keep track of this ?
tks & rdgs
maxzsimHi maxzsim
I think SQL Profiler can help you in achieving this task.
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:
> Hi,
> Does SQL provide an Audit log on who is accessing which database and what's
> being done on each table that's kept in certain system tables or is there any
> stored procedures to keep track of this ?
> tks & rdgs
> maxzsim|||Hi Chandra,
is there any alternative besides the SQL Profiler ?
rdgs
"Chandra" wrote:
> Hi maxzsim
> I think SQL Profiler can help you in achieving this task.
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> ---
>
> "maxzsim" wrote:
> > Hi,
> >
> > Does SQL provide an Audit log on who is accessing which database and what's
> > being done on each table that's kept in certain system tables or is there any
> > stored procedures to keep track of this ?
> >
> > tks & rdgs
> > maxzsim|||Hi maxzsim
You can use NT EventLog for this, but it will not give you all the
information that you require. I prefer using SQL Profiler for this purpose.
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:
> Hi Chandra,
> is there any alternative besides the SQL Profiler ?
> rdgs
> "Chandra" wrote:
> > Hi maxzsim
> > I think SQL Profiler can help you in achieving this task.
> >
> >
> > --
> > best Regards,
> > Chandra
> > http://chanduas.blogspot.com/
> > ---
> >
> >
> >
> > "maxzsim" wrote:
> >
> > > Hi,
> > >
> > > Does SQL provide an Audit log on who is accessing which database and what's
> > > being done on each table that's kept in certain system tables or is there any
> > > stored procedures to keep track of this ?
> > >
> > > tks & rdgs
> > > maxzsim|||Hi,
I recommend you to enable server side tracing if the server is Production.
This will use the resouce very less compared to SQL Profiler.
Have a look into the below great article from Vyas on server side tracing
with examples to configure:-
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:6485BF63-1708-4D96-B245-E056D12EEEAD@.microsoft.com...
> Hi,
> Does SQL provide an Audit log on who is accessing which database and
what's
> being done on each table that's kept in certain system tables or is there
any
> stored procedures to keep track of this ?
> tks & rdgs
> maxzsim|||An alternative might be to look at many of the transaction log analyzers out
there:
http://www.apexsql.com/sql_tools_log.htm
http://www.lumigent.com/products/entegra.html
http://www.logpi.com/
to name but three.
"maxzsim" wrote:
> Hi,
> Does SQL provide an Audit log on who is accessing which database and what's
> being done on each table that's kept in certain system tables or is there any
> stored procedures to keep track of this ?
> tks & rdgs
> maxzsim|||It might be worth mentioning that SELECT cannot be obtained from the transaction log. So the tools
that can audit reads do this using a background profiler trace...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scrat" <Scrat@.discussions.microsoft.com> wrote in message
news:19322556-D9C2-467F-ABBA-BC1C8845E222@.microsoft.com...
> An alternative might be to look at many of the transaction log analyzers out
> there:
> http://www.apexsql.com/sql_tools_log.htm
> http://www.lumigent.com/products/entegra.html
> http://www.logpi.com/
> to name but three.
> "maxzsim" wrote:
>> Hi,
>> Does SQL provide an Audit log on who is accessing which database and what's
>> being done on each table that's kept in certain system tables or is there any
>> stored procedures to keep track of this ?
>> tks & rdgs
>> maxzsim

Audit log that SA cannot modify

Anyone familiar with ways (SQL Server 2000, or 2005) to have an audit
log of who does what in the database (outside of a Great Plains front
end passing over requests) - that the SA cannot modify? If the SA - or
anyone - can modify the log - its no good from an audit perspective.
It has to be read-only. Any software packages out there that also do
this and present the log in a form thats easy to query / review?
Thanks!
Jason ShohetHi
In SQL Server 2005 you will be able to define a trigger on database level to
capture events.
<jasonshohet@.gmail.com> wrote in message
news:1139999866.893906.89260@.z14g2000cwz.googlegroups.com...
> Anyone familiar with ways (SQL Server 2000, or 2005) to have an audit
> log of who does what in the database (outside of a Great Plains front
> end passing over requests) - that the SA cannot modify? If the SA - or
> anyone - can modify the log - its no good from an audit perspective.
> It has to be read-only. Any software packages out there that also do
> this and present the log in a form thats easy to query / review?
> Thanks!
> Jason Shohet
>|||But the SA can disable the trigger, thats not enough.
I want something that can audit the SA himself - and anyone else. It
should report on all schema changes and all transactions made to the db
- by anyone - and nobody should be able to modify it (including the SA)
except truncate the log by date range at the end of the audit period.|||Hi
Don't you trust in SA? :-))))))
Remove people that you don't want from sysadmin server role and then you
audit them by using triggers
<jasonshohet@.gmail.com> wrote in message
news:1140012486.489277.187450@.g14g2000cwa.googlegroups.com...
> But the SA can disable the trigger, thats not enough.
> I want something that can audit the SA himself - and anyone else. It
> should report on all schema changes and all transactions made to the db
> - by anyone - and nobody should be able to modify it (including the SA)
> except truncate the log by date range at the end of the audit period.
>|||Ha, the issue is that the SA needs to be able to do this himself
but the SA role is necessary to perform maintenance on the SQL Server I
assume. Isn't there something that a QA person can install with the SA
watching perhaps - eg. a 3rd party logger, that can audit all
activities, that the SA cannot interfere with once installed. Pie in
the sky?|||Not pie in the sky. You can look at options with SQL Server
such as server side traces, maybe c2 auditing. Lots of third
party products that monitor activity - more products now
with SOX requirements. A couple of many would be AuditDB
from Lumigent: http://www.lumigent.com/products/auditdb.html
and Compliance Manager from Idera:
http://www.idera.com/Products/SQLcm/
-Sue
On 15 Feb 2006 14:15:29 -0800, jasonshohet@.gmail.com wrote:

>Ha, the issue is that the SA needs to be able to do this himself
>but the SA role is necessary to perform maintenance on the SQL Server I
>assume. Isn't there something that a QA person can install with the SA
>watching perhaps - eg. a 3rd party logger, that can audit all
>activities, that the SA cannot interfere with once installed. Pie in
>the sky?

Audit log not logging

As there is two types of auditing, have a look at the
following article
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_security_2ard.asp, if its
C2 auditing then it needs to be used in the same line as
starting your SQL Server service.
Peter
"Denial ain't just a river in Egypt."
Mark Twain

>--Original Message--
>Thank you for your time.
>I have a database that i need to have auditing on. The
database was moved
>to a larger server. I had auditing on before the move,
but now in my SQL
>Sercer audit log I only have the error message 15457
Severity: 0, State: 1.
>I need auditing on for this database, and have not been
able to find
>anything online that explaines what might have caused the
logging to stop or
>how to fix this issue. The users can still login, but
SQL Server isn't
>auditing it anymore.
>Thank you again for your time.
>.
>
Thank you for the article. That is what I was trying to audit. I had the
auditing working before the database was moved, but now I only get the error
message in the audit log. I didn't know about the SQL Profiler before.
Thanks for pointing that out.
"Peter The Spate" wrote:

> As there is two types of auditing, have a look at the
> following article
> http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/adminsql/ad_security_2ard.asp, if its
> C2 auditing then it needs to be used in the same line as
> starting your SQL Server service.
> Peter
> "Denial ain't just a river in Egypt."
> Mark Twain
>
> database was moved
> but now in my SQL
> Severity: 0, State: 1.
> able to find
> logging to stop or
> SQL Server isn't
>

Audit log not logging

Thank you for your time.
I have a database that i need to have auditing on. The database was moved
to a larger server. I had auditing on before the move, but now in my SQL
Sercer audit log I only have the error message 15457 Severity: 0, State: 1.
I need auditing on for this database, and have not been able to find
anything online that explaines what might have caused the logging to stop or
how to fix this issue. The users can still login, but SQL Server isn't
auditing it anymore.
Thank you again for your time.
What were you auditing? Perhaps there was a startup param set on your old
server. Perhaps you chose to auditing logins via Enterprise Manager (right
click on the server name, properties, Security tab).
Keith
"Marc M" <Marc M@.discussions.microsoft.com> wrote in message
news:A89E9DBD-9C96-400B-8208-6AA7EF86141F@.microsoft.com...
> Thank you for your time.
> I have a database that i need to have auditing on. The database was moved
> to a larger server. I had auditing on before the move, but now in my SQL
> Sercer audit log I only have the error message 15457 Severity: 0, State:
1.
> I need auditing on for this database, and have not been able to find
> anything online that explaines what might have caused the logging to stop
or
> how to fix this issue. The users can still login, but SQL Server isn't
> auditing it anymore.
> Thank you again for your time.
|||Thank you for the suggestion to check the old server, I'll look at that to
see if there is anything running that we didn't get moved.
We were trying to audit the logins and logouts of the users of the database.
I already have in place the auditing through Enterprise Manager, and were
looking at it throught the logs associated with that. I really apprecitate
the level of detail provided on how to activeate it and would apprecitate
that level of detail on the solution for why we are only getting the error
15457 in those logs.
Thank you for your help.
"Keith Kratochvil" wrote:

> What were you auditing? Perhaps there was a startup param set on your old
> server. Perhaps you chose to auditing logins via Enterprise Manager (right
> click on the server name, properties, Security tab).
> --
> Keith
>
> "Marc M" <Marc M@.discussions.microsoft.com> wrote in message
> news:A89E9DBD-9C96-400B-8208-6AA7EF86141F@.microsoft.com...
> 1.
> or
>

Audit log not logging

Thank you for your time.
I have a database that i need to have auditing on. The database was moved
to a larger server. I had auditing on before the move, but now in my SQL
Sercer audit log I only have the error message 15457 Severity: 0, State: 1.
I need auditing on for this database, and have not been able to find
anything online that explaines what might have caused the logging to stop or
how to fix this issue. The users can still login, but SQL Server isn't
auditing it anymore.
Thank you again for your time.As there is two types of auditing, have a look at the
following article
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_security_2ard.asp, if its
C2 auditing then it needs to be used in the same line as
starting your SQL Server service.
Peter
"Denial ain't just a river in Egypt."
Mark Twain
>--Original Message--
>Thank you for your time.
>I have a database that i need to have auditing on. The
database was moved
>to a larger server. I had auditing on before the move,
but now in my SQL
>Sercer audit log I only have the error message 15457
Severity: 0, State: 1.
>I need auditing on for this database, and have not been
able to find
>anything online that explaines what might have caused the
logging to stop or
>how to fix this issue. The users can still login, but
SQL Server isn't
>auditing it anymore.
>Thank you again for your time.
>.
>|||What were you auditing? Perhaps there was a startup param set on your old
server. Perhaps you chose to auditing logins via Enterprise Manager (right
click on the server name, properties, Security tab).
--
Keith
"Marc M" <Marc M@.discussions.microsoft.com> wrote in message
news:A89E9DBD-9C96-400B-8208-6AA7EF86141F@.microsoft.com...
> Thank you for your time.
> I have a database that i need to have auditing on. The database was moved
> to a larger server. I had auditing on before the move, but now in my SQL
> Sercer audit log I only have the error message 15457 Severity: 0, State:
1.
> I need auditing on for this database, and have not been able to find
> anything online that explaines what might have caused the logging to stop
or
> how to fix this issue. The users can still login, but SQL Server isn't
> auditing it anymore.
> Thank you again for your time.|||Thank you for the article. That is what I was trying to audit. I had the
auditing working before the database was moved, but now I only get the error
message in the audit log. I didn't know about the SQL Profiler before.
Thanks for pointing that out.
"Peter The Spate" wrote:
> As there is two types of auditing, have a look at the
> following article
> http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/adminsql/ad_security_2ard.asp, if its
> C2 auditing then it needs to be used in the same line as
> starting your SQL Server service.
> Peter
> "Denial ain't just a river in Egypt."
> Mark Twain
>
> >--Original Message--
> >Thank you for your time.
> >I have a database that i need to have auditing on. The
> database was moved
> >to a larger server. I had auditing on before the move,
> but now in my SQL
> >Sercer audit log I only have the error message 15457
> Severity: 0, State: 1.
> >I need auditing on for this database, and have not been
> able to find
> >anything online that explaines what might have caused the
> logging to stop or
> >how to fix this issue. The users can still login, but
> SQL Server isn't
> >auditing it anymore.
> >Thank you again for your time.
> >.
> >
>|||Thank you for the suggestion to check the old server, I'll look at that to
see if there is anything running that we didn't get moved.
We were trying to audit the logins and logouts of the users of the database.
I already have in place the auditing through Enterprise Manager, and were
looking at it throught the logs associated with that. I really apprecitate
the level of detail provided on how to activeate it and would apprecitate
that level of detail on the solution for why we are only getting the error
15457 in those logs.
Thank you for your help.
"Keith Kratochvil" wrote:
> What were you auditing? Perhaps there was a startup param set on your old
> server. Perhaps you chose to auditing logins via Enterprise Manager (right
> click on the server name, properties, Security tab).
> --
> Keith
>
> "Marc M" <Marc M@.discussions.microsoft.com> wrote in message
> news:A89E9DBD-9C96-400B-8208-6AA7EF86141F@.microsoft.com...
> > Thank you for your time.
> > I have a database that i need to have auditing on. The database was moved
> > to a larger server. I had auditing on before the move, but now in my SQL
> > Sercer audit log I only have the error message 15457 Severity: 0, State:
> 1.
> > I need auditing on for this database, and have not been able to find
> > anything online that explaines what might have caused the logging to stop
> or
> > how to fix this issue. The users can still login, but SQL Server isn't
> > auditing it anymore.
> > Thank you again for your time.
>

Audit Log in Yukon

Hi There,
Can someone tell me if there is any difference in the audit log available in
SQL Server 2000 and Yukon?
TIA
irfan
Do you mean the Audit Level server setting that writes entries to the SQL
Error Log? The biggest change is the logging of the IP address. There are
also a number of other options for collecting this information e.g. the
default trace or event notifications (via TSQL or WMI). In earlier builds
the Audit Level was set to All by default but sanity seems to have prevailed
and the default setting is now Failure.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Irfan" <Irfan@.discussions.microsoft.com> wrote in message
news:D112D80B-A00F-4D37-85AB-F0B3A96F4542@.microsoft.com...
> Hi There,
> Can someone tell me if there is any difference in the audit log available
> in
> SQL Server 2000 and Yukon?
> TIA
> irfan

Audit log in 3-tiered applicaiton

In the past, a user would run a Windows application that connected
directly to the database. When that user made changes, we used a
trigger to log those changes to a separate table. We used Current_User
to record who made the changes, Current_TimeStamp to record when and
values out of the inserted or deleted table(s) to record what was
changed. Easy enough.
How can we create a log of changes in a 3-tiered windows application?
The user never touches the database. The account that executes the
middle tier is the same for every user. The GUI (vb.net) can easily
determine the Windows ID of the application user. The GUI already
passes that ID to the middle tier for security reasons. But then what?
Every idea I come up with involves calling stored procs from either the
middle tier or from the application's stored procs. That seems like a
lot of work and triggers were so easy for this task.
Any ideas are appreciated.
Tom the lazy programmer.
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.Does the database not record what logged-in user (logged into the
application, not the database) is making changes? If not, why not? It
seems like it would make sense to record that anyway -- and this would
enable your trigger solution to work once again... Or am I missing
something?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Tom Williams" <Tom.Williams@.NOSPAMncmail.net> wrote in message
news:e2PbWjkLFHA.1308@.tk2msftngp13.phx.gbl...
> In the past, a user would run a Windows application that connected
> directly to the database. When that user made changes, we used a
> trigger to log those changes to a separate table. We used Current_User
> to record who made the changes, Current_TimeStamp to record when and
> values out of the inserted or deleted table(s) to record what was
> changed. Easy enough.
> How can we create a log of changes in a 3-tiered windows application?
> The user never touches the database. The account that executes the
> middle tier is the same for every user. The GUI (vb.net) can easily
> determine the Windows ID of the application user. The GUI already
> passes that ID to the middle tier for security reasons. But then what?
> Every idea I come up with involves calling stored procs from either the
> middle tier or from the application's stored procs. That seems like a
> lot of work and triggers were so easy for this task.
> Any ideas are appreciated.
> Tom the lazy programmer.
>
> --
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.
>|||Please pardon my ignorance, but I'm .
Our users log into a Windows domain and would then execute the Windows
application(GUI). I don't know what you mean by "logged into the
application". The application does not have it's own user IDs and
passwords. Our users have too many of those already. If they've
already logged into Windows, that's good enough for me. The GUI will
make function calls to the middle tier which will then access the
database.
Are you saying that there is a function in the database that can return
which Windows user called the middle tier function?
thanks
Tom
Adam Machanic wrote:

>Does the database not record what logged-in user (logged into the
>application, not the database) is making changes? If not, why not? It
>seems like it would make sense to record that anyway -- and this would
>enable your trigger solution to work once again... Or am I missing
>something?
>
>
"Tom Williams" <Tom.Williams@.NOSPAMncmail.net> wrote in message
news:e2PbWjkLFHA.1308@.tk2msftngp13.phx.gbl...

> In the past, a user would run a Windows application that connected
> directly to the database. When that user made changes, we used a
> trigger to log those changes to a separate table. We used Current_User
> to record who made the changes, Current_TimeStamp to record when and
> values out of the inserted or deleted table(s) to record what was
> changed. Easy enough.
> How can we create a log of changes in a 3-tiered windows application?
> The user never touches the database. The account that executes the
> middle tier is the same for every user. The GUI (vb.net) can easily
> determine the Windows ID of the application user. The GUI already
> passes that ID to the middle tier for security reasons. But then what?
> Every idea I come up with involves calling stored procs from either the
> middle tier or from the application's stored procs. That seems like a
> lot of work and triggers were so easy for this task.
> Any ideas are appreciated.
> Tom the lazy programmer.
>
>
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.|||No; I made the assumption that your application required credentails or a
login of some sort -- which apparently it does (domain authentication). So
the question is, can the front-end pass the logged-in users' name to the
middle tier, which will then pass it into the database? And can a UserName
column be added to each table that the database manipulates, such that the
trigger once again becomes a viable option?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Tom Williams" <Tom.Williams@.NOSPAMncmail.net> wrote in message
news:%23gmx8dlLFHA.436@.TK2MSFTNGP09.phx.gbl...
> Please pardon my ignorance, but I'm .
> Our users log into a Windows domain and would then execute the Windows
> application(GUI). I don't know what you mean by "logged into the
> application". The application does not have it's own user IDs and
> passwords. Our users have too many of those already. If they've
> already logged into Windows, that's good enough for me. The GUI will
> make function calls to the middle tier which will then access the
> database.
> Are you saying that there is a function in the database that can return
> which Windows user called the middle tier function?
> thanks
> Tom
>|||Oh, I see!
I'll have to think about whether we want to add that column or not.
Thanks for the suggestion.
Tom
Adam Machanic wrote:

>No; I made the assumption that your application required credentails or a
>login of some sort -- which apparently it does (domain authentication). So
>the question is, can the front-end pass the logged-in users' name to the
>middle tier, which will then pass it into the database? And can a UserName
>column be added to each table that the database manipulates, such that the
>trigger once again becomes a viable option?
>
>
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.|||Tom Williams wrote:
> In the past, a user would run a Windows application that connected
> directly to the database. When that user made changes, we used a
> trigger to log those changes to a separate table. We used Current_User
> to record who made the changes, Current_TimeStamp to record when and
> values out of the inserted or deleted table(s) to record what was
> changed. Easy enough.
> How can we create a log of changes in a 3-tiered windows application?
> The user never touches the database. The account that executes the
> middle tier is the same for every user. The GUI (vb.net) can easily
> determine the Windows ID of the application user. The GUI already
> passes that ID to the middle tier for security reasons. But then what?
Store the ID using SET CONTEXT_INFO, then have the triggers read that
back and write it in your audit trail. See BOL for SET CONTEXT_INFO.
Steve Troxell
http://www.omniaudit.com

audit log

Excuse the naive question, but I have heard about SQL Server's "transaction
log" and I am wondering if this can be viewed to see all of the transactions
that occurred in past X amount of time. Is this feasible?
Are than any other solutions for audit trail besides putting triggers on
every table to log everything?
Thanks.
You can "view" the transaction log using third-party programs like LogPI and
Log Explorer (Google for them) but it's not a very pleasant experience --
there's a surprisingly large amount of data to sort through. Triggers are
definitely a good bet for a manageable audit trail in the database. You can
also look at SQL Server's built-in auditing facilities. Check out the
topic, "Auditing SQL Server Activity" in BOL.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Mike W" <mikeotown@.nospam.msn.com> wrote in message
news:eMcABUdFFHA.4004@.tk2msftngp13.phx.gbl...
> Excuse the naive question, but I have heard about SQL Server's
"transaction
> log" and I am wondering if this can be viewed to see all of the
transactions
> that occurred in past X amount of time. Is this feasible?
> Are than any other solutions for audit trail besides putting triggers on
> every table to log everything?
> Thanks.
>
|||Here are some links: http://vyaskn.tripod.com/administration_faq.htm#q1
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mike W" <mikeotown@.nospam.msn.com> wrote in message
news:eMcABUdFFHA.4004@.tk2msftngp13.phx.gbl...
Excuse the naive question, but I have heard about SQL Server's "transaction
log" and I am wondering if this can be viewed to see all of the transactions
that occurred in past X amount of time. Is this feasible?
Are than any other solutions for audit trail besides putting triggers on
every table to log everything?
Thanks.
|||If you talking detailed auditing such as might be required by the dreaded
Sarbnes/Oxely Act, then there are also some third party (read - not cheap)
tools for such things. Lumigent is one company I know of.
Bob Castleman
DBA Poseur
"Mike W" <mikeotown@.nospam.msn.com> wrote in message
news:eMcABUdFFHA.4004@.tk2msftngp13.phx.gbl...
> Excuse the naive question, but I have heard about SQL Server's
> "transaction log" and I am wondering if this can be viewed to see all of
> the transactions that occurred in past X amount of time. Is this
> feasible?
> Are than any other solutions for audit trail besides putting triggers on
> every table to log everything?
> Thanks.
>

audit log

Excuse the naive question, but I have heard about SQL Server's "transaction
log" and I am wondering if this can be viewed to see all of the transactions
that occurred in past X amount of time. Is this feasible?
Are than any other solutions for audit trail besides putting triggers on
every table to log everything?
Thanks.You can "view" the transaction log using third-party programs like LogPI and
Log Explorer (Google for them) but it's not a very pleasant experience --
there's a surprisingly large amount of data to sort through. Triggers are
definitely a good bet for a manageable audit trail in the database. You can
also look at SQL Server's built-in auditing facilities. Check out the
topic, "Auditing SQL Server Activity" in BOL.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mike W" <mikeotown@.nospam.msn.com> wrote in message
news:eMcABUdFFHA.4004@.tk2msftngp13.phx.gbl...
> Excuse the naive question, but I have heard about SQL Server's
"transaction
> log" and I am wondering if this can be viewed to see all of the
transactions
> that occurred in past X amount of time. Is this feasible?
> Are than any other solutions for audit trail besides putting triggers on
> every table to log everything?
> Thanks.
>|||Here are some links: http://vyaskn.tripod.com/administration_faq.htm#q1
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mike W" <mikeotown@.nospam.msn.com> wrote in message
news:eMcABUdFFHA.4004@.tk2msftngp13.phx.gbl...
Excuse the naive question, but I have heard about SQL Server's "transaction
log" and I am wondering if this can be viewed to see all of the transactions
that occurred in past X amount of time. Is this feasible?
Are than any other solutions for audit trail besides putting triggers on
every table to log everything?
Thanks.|||If you talking detailed auditing such as might be required by the dreaded
Sarbnes/Oxely Act, then there are also some third party (read - not cheap)
tools for such things. Lumigent is one company I know of.
Bob Castleman
DBA Poseur
"Mike W" <mikeotown@.nospam.msn.com> wrote in message
news:eMcABUdFFHA.4004@.tk2msftngp13.phx.gbl...
> Excuse the naive question, but I have heard about SQL Server's
> "transaction log" and I am wondering if this can be viewed to see all of
> the transactions that occurred in past X amount of time. Is this
> feasible?
> Are than any other solutions for audit trail besides putting triggers on
> every table to log everything?
> Thanks.
>

audit log

Are there any sys tables that store audit logs for
commands such as sp_changeobjectowner? Is this a feature
that can be turned on? If so where?
Thanks in advance :-)
TylerTyler,
Check the Profiler tool. It comes with SQL Server and is described in Books
OnLine. You can check some 3rd party tools as well, for example check
www.lumigent.com.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"tyler" <e_tyler@.netzero.net> wrote in message
news:0cc801c35c2b$5e5edc10$a501280a@.phx.gbl...
> Are there any sys tables that store audit logs for
> commands such as sp_changeobjectowner? Is this a feature
> that can be turned on? If so where?
> Thanks in advance :-)
> Tyler