Audit Trail - getting the current username.

Hi, I have a couple of questions on writing an audit trail/last updated
feature for my database.
First, How can I programatically get the current user in SQL when
executing a stored procedure.I have tried using sysusers table and
CURRENT_USER but only get the value dbo. I am using integrated
security. sp_who seesm to have the data but how can i use it?
Second, is it actually prefered approach to pass this value in from IIS
or the calling application as a parameter? I'd prefer it be hardcoded
in the stoerd procedure. Are there any divantages to this ?
Third question, I am doing this for a simple audit of LastUpdated and
LastUpDatedBy with additional columns on tables (see below), I know a
trigger and history table is much more comprehensive, but are there any
other approaches to consider or built in tools for tracking changes to
records and capturing who/when data.
thanks
hals_left
CREATE PROCEDURE [add_record]
@.RecordID smallInt,
AS
Begin
INSERT INTO myTable(RecordID,LastUpdated,LastUpdated
By )
VALUES ( @.RecordID , GetDate(), CURRENT_USER )
End
GOSELECT SYSTEM_USER
That will return the SQL login name or the windows domain and username.
HTH
--
Gail Shaw (MCSD)
http://gail.rucus.net/
"cc900630@.ntu.ac.uk" wrote:

> Hi, I have a couple of questions on writing an audit trail/last updated
> feature for my database.
> First, How can I programatically get the current user in SQL when
> executing a stored procedure.I have tried using sysusers table and
> CURRENT_USER but only get the value dbo. I am using integrated
> security. sp_who seesm to have the data but how can i use it?
> Second, is it actually prefered approach to pass this value in from IIS
> or the calling application as a parameter? I'd prefer it be hardcoded
> in the stoerd procedure. Are there any divantages to this ?
> Third question, I am doing this for a simple audit of LastUpdated and
> LastUpDatedBy with additional columns on tables (see below), I know a
> trigger and history table is much more comprehensive, but are there any
> other approaches to consider or built in tools for tracking changes to
> records and capturing who/when data.
> thanks
> hals_left
> CREATE PROCEDURE [add_record]
> @.RecordID smallInt,
> AS
> Begin
> INSERT INTO myTable(RecordID,LastUpdated,LastUpdated
By )
> VALUES ( @.RecordID , GetDate(), CURRENT_USER )
> End
> GO
>|||
"cc900630@.ntu.ac.uk" schrieb:

> Hi, I have a couple of questions on writing an audit trail/last updated
> feature for my database.
> First, How can I programatically get the current user in SQL when
> executing a stored procedure.I have tried using sysusers table and
> CURRENT_USER but only get the value dbo. I am using integrated
> security. sp_who seesm to have the data but how can i use it?
> Second, is it actually prefered approach to pass this value in from IIS
> or the calling application as a parameter? I'd prefer it be hardcoded
> in the stoerd procedure. Are there any divantages to this ?
> Third question, I am doing this for a simple audit of LastUpdated and
> LastUpDatedBy with additional columns on tables (see below), I know a
> trigger and history table is much more comprehensive, but are there any
> other approaches to consider or built in tools for tracking changes to
> records and capturing who/when data.
> thanks
> hals_left
> CREATE PROCEDURE [add_record]
> @.RecordID smallInt,
> AS
> Begin
> INSERT INTO myTable(RecordID,LastUpdated,LastUpdated
By )
> VALUES ( @.RecordID , GetDate(), CURRENT_USER )
> End
> GO
Try SUser_SName() ...|||Hi,
For the first question:
You can capture the current user in SQL using SYSTEM_USER.
For the second question:
When you can capture this in SQL through SYSTEM_USER keyword, i hope you can
avoid hardcoding.
For the third Question:
You can launch the enterprise manager. Right click on the database to look
for properties. In the properties window, go to the security tab and set the
Audit level to your choice.After you change audit settings, you need to
restart the server. But this writes to Application Log might degrade
performance. I think the approach you are using should be fair enough.
I hope this will be of some help to you.
"cc900630@.ntu.ac.uk" wrote:

> Hi, I have a couple of questions on writing an audit trail/last updated
> feature for my database.
> First, How can I programatically get the current user in SQL when
> executing a stored procedure.I have tried using sysusers table and
> CURRENT_USER but only get the value dbo. I am using integrated
> security. sp_who seesm to have the data but how can i use it?
> Second, is it actually prefered approach to pass this value in from IIS
> or the calling application as a parameter? I'd prefer it be hardcoded
> in the stoerd procedure. Are there any divantages to this ?
> Third question, I am doing this for a simple audit of LastUpdated and
> LastUpDatedBy with additional columns on tables (see below), I know a
> trigger and history table is much more comprehensive, but are there any
> other approaches to consider or built in tools for tracking changes to
> records and capturing who/when data.
> thanks
> hals_left
> CREATE PROCEDURE [add_record]
> @.RecordID smallInt,
> AS
> Begin
> INSERT INTO myTable(RecordID,LastUpdated,LastUpdated
By )
> VALUES ( @.RecordID , GetDate(), CURRENT_USER )
> End
> GO
>