How can I log user name when SQL Server database o

Question

I have created ASP.NET web forms for editing data and the underlying database tables have associated audit tables with the user name, date, and changed data that are filled using triggers. The SQL Server database is on a different server than the web server. The requirement is that the individual user names be logged in the audit tables. The only way I found to do this is the following: • SQL Connection string specifies Integrated Security, and • IIS Authentication specifies ASP.NET Impersonation and Basic Authentication. In order to use the application, a user enters their windows credentials to log on to the web server and to access the underlying SQL Server database and must be part of a group that has read/write/execute stored procedure permissions. Is this the only way to accomplish this without using Forms Authentication and an associated 'Users' table? For an internal web site, is this considered secure enough?

Answer 1

If you want to use Forms Authentication, you'll need to use Impersonation.

Example table:

create table foo (
  id int primary key,
  bar varchar(255) not null,
  inserted_by_user varchar(255) default current_user
);

This requires some help from your database connection pool.

When you get your connection from the pool, run this:

execute as user = 'alice'; -- where alice is the logged in user's username

Then run your normal SQL queries that you were planning on running:

insert into foo (id, bar) values (1, 'whatever');

When you release your connection back to the pool run this:

revert;

When you look at table foo, you should see this:

id  bar       inserted_by_user
------------------------------
1   whatever  alice