audit tables, delete triggers and sql server authentication

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

Re: audit tables, delete triggers and sql server authentication

ecastillo wrote:
?
>
> (i'm hoping a built-in function exists that solves all my problems.)
>
No, there isn't. The SQL Server only knows the name that was used to log
into it. How could it possibly have knowledge of credentials that were not
passed to it? As I see it, you have two options:

1. Use Windows Authentication and deal with te issues that arise from that
choice.
2. Do all data updates via stored procedures to which you pass the users'
Windows credentials.

I prefer option 2, since I prefer to do everything by stored procedure
anyways, meaning tat I started writing the app using them. I can understand
why you would hesitate to use this option if you've already got the app
written using dynamic sql (ugh!).

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"