audit tables, delete triggers and sql server authentication

From: ecastillo (encee5_at_gmail.com)
Date: 11/26/04 

Date: 25 Nov 2004 20:46:48 -0800

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

Relevant Pages

  • DirectoryServices
    ... A system that is being designed will use ActiveDirectory to store ... userinformation for a SQL Server 200 base web app. ... to AD after getting the windwos user account from SQL Server. ...
    (microsoft.public.dotnet.framework)
  • creating .mdf in ASP
    ... I've been writing Windows apps for years now but to become familiar with web apps I'm working my way through some instructions to build a web site using a SQL database. ... Connections to SQL Server filesrequire SQL Server Express 2005 to function properly. ... I expect that the production system is being seen as the default SQL Server by my pc and consequently the web app is trying to connect to it to create the database. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Login failed for user NT AUTHORITYNETWORK SERVICE.
    ... If so you should either create an account for the web app on the sql server and use that account to connect or if you must use windows auth to connect to the sql server then you need to enable impersonation in iis and your web app so that it accesses the db with the credentials of the user that's using the web app through IE. ...
    (microsoft.public.sqlserver.security)
  • inserting Turkish chars into sql server
    ... I have ASP.NET web app and SQL Server 2k database. ... The same results are produced when I run the query from the query analyzer. ...
    (microsoft.public.dotnet.framework.adonet)