0

I'm looking for suggestions on how developers pass the identity of a their logged in user to the database when making updates.

To keep it simple, imagine we are using simple ADO.NET SQLCommand to insert some rows into a log table. The row has a UserId column which needs populated with the logged in user rather than the generic userId used on the connection string.

Obviously, the SQLCommand could accept in a parameter of "UserId" and simply use this. But I'm wondering if there are any better options to save having to manually pass this for every command.

I've looked at using Azure AD to provide ADFS for logging into the web application, then using this identity on the connection string to the database. This works well, but I am concerned that we are giving an application user account direct access to the database - this might be ok, but seems like a risk. If a malicious user some how managed to get SSMS to our DB server, they'd have a username and password they could use.

Additionally, if we were not using AD to manage our user accounts - ASP.Net Membership for example... what could we do here? Are their any properties we can set at connection time?

Thanks David

Improve this question

2 Answers 2

Reset to default
1

Check out the new SESSION_CONTEXT() function, which was added for just this scenario.

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

Comments

0

Please use T-SQL calls sp_who, or select suser_name() They show the current login of a user who is conneted and doing T-SQL operation Thanks Mirek

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.