0

I have a need to support extensive auditing capabilities for a system backing into Sql Server 2008. Since I plan to use LINQ (with no Stored Procs), the database would be a clean, zero contact data repository.

However, I need to pratically record a snapshot of every change that happens in the db. So, I thought I should use triggers. But then, I need a user id for the particular user (not the connection string user id) to flow through into the database.

In oracle, I should have been able to set up a PROXY USER and the trigger would be able to pick that up. Last I checked, there was no proxy user concept in Sql Server.

Does anyone know if there's any extender property I can use to flow through my authenticated user name?

ps: I don't mind the impact on connection pooling (if any).

Thanks. P

Improve this question

2 Answers 2

Reset to default
2

I haven't tried this, but from the parameters in a connection string, these two (probably more) can be retrieved in T-SQL: APP_NAME() and HOST_NAME().

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I was thinking of host_name() but I thought there'd be something less 'hacky'. I'll just use host_name() then. Thanks, @devio. P
0

I solved this problem by associating the @@SPID with the current User ID.

You need a table with the structure SPID int, UserID int, and an SP with the same parameters. The SP deletes the record with the SPID if it exists, and inserts the new pair.

In the trigger, you can retrieve the User ID by querying this table with @@SPID.

The SP needs to be called whenever a connection is opened. (you did not mention how you want to access the db)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.