Hi.

I'm using ASP.NET Membership and Role Management  (Forms authentication) to
secure my web application (accessible to a restricted user base via the
Internet). This is such a great feature of ASP.NET 2.0. My app accesses data
in a Sql Server database which is running on the same box as IIS.

For certain operations on certain tables, I need to keep a log of who did
what and when. For example, I need to record the name of the person who
creates a row in a particular table. To do this I have built a trigger that
inserts a row into an "Event" table. That part was simple.

My problem is getting the name of the user that is logged in to ASP.NET.
SqlContext.WindowsIdentity only works if I am using integrated security. But
this model is not compatible with ASP.NET Forms authentication.

That leaves me with SUSER_NAME(). But in order to use that I need to insert
the user name and password into the connection string and use Sql Server
mixed mode authentication. Then I create a Sql Server login for each users
I've added to the Membership database.

This seems like an administrative nightmare. Is there another way?

Thanks,
Mike

Pass the asp.net username as a parameter in all DML procedures that produce
the consequences which you want to log. To use triggers, you'd also need to
add a username column to each table. So, I'd suggest using a separate
procedure to log events.


ML

Thanks, ML. Very clever idea. I add a user name column and my trigger reads
that and logs the event. Each transaction could come from different users and
overwrite the user name, even for a single row, but because the trigger will
get called once per transaction, it will get hold of the correct user each
time. Brilliant.

I specifically want to log events using triggers since certain users will be
using admin tools to edit data. In that case I can default the user name to
SUSER_NAME(). What did you have in mind with a separate procedure to log the
event (i.e. please elaborate)?

The other option I mentioned is based on a presumption that all DML is done
through procedures, but you mention other tools.

E.g.:
Inside your typical DML procedure you begin a transaction, do data
manipulation, and if no errors are present you commit the transaction. If the
process needs to be logged, then the logging procedure should be called
before the acual commit.

This way you can avoid logging every change - for instance in case of batch
processing, where the entire batch needs to be logged rather than each
elementary process.


ML

INSERT table (column1, column2) (@variable, '100')
Simple JOIN turned ugly - Help!
Is the SQL Server Service Running?
Grouping,Paging Logic
sORTING ACROSS MULTIPLE COLUMN