audit tables, delete triggers, and asp.net

[ecastillo]

i'm in a bit of a bind at work. if anyone could help, i'd greatly
appreciate it.

i have a web app connecting to a sql server using sql server
authentication. let's say, for example, my login/password is
dbUser/dbUser. the web app however, is using windows authentication.
so if I am logged into the network as 'DOMAIN\Eric', when I access my
web app, my web app knows that I am 'DOMAIN\Eric'. but to the sql
server db, I am user 'dbUser'.

now, i for each table i have, i need to implement an audit table to
record all updates, inserts, deletes that occur against it. i was
going to do so with triggers. this is all fine for selects, inserts,
and updates. for each table, i have an updatedby and an updatedate.

for example, let's say i have a table:

create table blah
(
id int,
col1 varchar(10),
updatedby varchar(30),
updatedate datetime
)

and corresponding audit table:

create audit_blah
(
id int,
blah_id int,
blah_col1 varchar(10),
blah_updatedby varchar(1),
blah_updatedate datetime
)

for update and insert triggers, i can know what to insert into the
updatedby column of audit_blah because it's in a corresponding row in
blah. my web app knows what user is accessing the application, and
can insert that name into blah. blah's trigger will then insert that
name into audit_blah.

however, in the case of a delete, i'm not passing in an 'updatedby',
because i'm deleting. in this situation, how can the trigger know
what user is deleting? the db only knows that sql user 'dbUser' is
deleting, but doesn't know that 'dbUser' is deleting on behalf of
'DOMAIN\Eric'. is there any way for my app to inform the trigger to
access my windows identity without having a corresponding row in the
table from which to pull that info?

obviously, i could have each of my app's users log into SQL server
through Windows authentication; then i could just use SYSTEM_USER.
but let's say, for performance's sake, it'd be better for me to use
one sql server login. (i believe one user works better for connection
pooling purposes.) is there a way to get around this?

(i'm hoping a built-in function exists that solves all my problems.)

suggestions? resources?

any help would be great appreciated.

happy turkeys.

Eric

[John Bell]

Hi

You may want to do soft deletes instead (possibly with a garbage collection
job!) or do the deletes through a stored procedure and log them differently.

John
"ecastillo" <en****@gmail.c om> wrote in message
news:a2******** *************** **@posting.goog le.com...

i'm in a bit of a bind at work. if anyone could help, i'd greatly
appreciate it.

i have a web app connecting to a sql server using sql server
authentication. let's say, for example, my login/password is
dbUser/dbUser. the web app however, is using windows authentication.
so if I am logged into the network as 'DOMAIN\Eric', when I access my
web app, my web app knows that I am 'DOMAIN\Eric'. but to the sql
server db, I am user 'dbUser'.

now, i for each table i have, i need to implement an audit table to
record all updates, inserts, deletes that occur against it. i was
going to do so with triggers. this is all fine for selects, inserts,
and updates. for each table, i have an updatedby and an updatedate.

for example, let's say i have a table:

create table blah
(
id int,
col1 varchar(10),
updatedby varchar(30),
updatedate datetime
)

and corresponding audit table:

create audit_blah
(
id int,
blah_id int,
blah_col1 varchar(10),
blah_updatedby varchar(1),
blah_updatedate datetime
)

for update and insert triggers, i can know what to insert into the
updatedby column of audit_blah because it's in a corresponding row in
blah. my web app knows what user is accessing the application, and
can insert that name into blah. blah's trigger will then insert that
name into audit_blah.

however, in the case of a delete, i'm not passing in an 'updatedby',
because i'm deleting. in this situation, how can the trigger know
what user is deleting? the db only knows that sql user 'dbUser' is
deleting, but doesn't know that 'dbUser' is deleting on behalf of
'DOMAIN\Eric'. is there any way for my app to inform the trigger to
access my windows identity without having a corresponding row in the
table from which to pull that info?

obviously, i could have each of my app's users log into SQL server
through Windows authentication; then i could just use SYSTEM_USER.
but let's say, for performance's sake, it'd be better for me to use
one sql server login. (i believe one user works better for connection
pooling purposes.) is there a way to get around this?

(i'm hoping a built-in function exists that solves all my problems.)

suggestions? resources?

any help would be great appreciated.

happy turkeys.

Eric

[Erland Sommarskog]

[posted and mailed, please reply in news]

ecastillo (en****@gmail.c om) writes:

however, in the case of a delete, i'm not passing in an 'updatedby',
because i'm deleting. in this situation, how can the trigger know
what user is deleting? the db only knows that sql user 'dbUser' is
deleting, but doesn't know that 'dbUser' is deleting on behalf of
'DOMAIN\Eric'. is there any way for my app to inform the trigger to
access my windows identity without having a corresponding row in the
table from which to pull that info?

You could use SET CONTEXT_INFO. This command is somewhat tricky to use,
but it's workable. This commands sets the column context_info in
sysprocesses. The value is a binary value. Here is an example:

declare @bin varbinary(30)
select @bin = convert(varbina ry(30), 'DOMAIN\Eric')
set context_info @bin
go
select convert(varchar (30), context_info)
from master..sysproc esses where spid = @@spid

The web server would do the first part, the trigger the second part.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp