CODEWITHSUNDEEP
asp.net-mvcazure-sql-database
Biff MaGriff
Biff MaGriff

Reputation: 8241

Is it possible to get the username in a database trigger when not using Windows authentication?

I went and created an azure website with simple AspNet authentication. I then painstakingly got simple trigger based auditing working with my azure database. It is all good except that the UserName is the db connection user name.

Is it possible to get the user name of the user logged into my application inside a DB trigger?

Upvotes: 0

Views: 220

Answers (1)

Gabe
Gabe

Reputation: 86768

There's no way for the trigger to know what user is logged in if you're not using SQL Server account.

One implementation strategy is to use stored procedures to do all updates. You would then make the username a parameter to each stored procedure.

Another possible strategy is to have a username column in each table that you would set on each insert or update, which the trigger would then have access to. I can't think of a way to audit deletions with this method, but that wouldn't be a problem if your database uses a deleted flag in each row rather than actually deleting rows.

Upvotes: 1

Related Questions