Returning Windows Logon Credentials to a SQL Query

Question

Post reply

Returning Windows Logon Credentials to a SQL Query

SteveEClarke

SSCertifiable

Points: 5161
More actions
January 29, 2013 at 1:57 am

#288077

Hi All,
I am using a trigger to update a table based on data changes - which is working well - but as an improvement I would like to store the Windows Login "Domain\Username" - ie. WORLD\Fred.Bloggs
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 1

Just to keep the post live - I have answered my own question already.

DECLARE @CurrentLogon Char(50)

Set @CurrentLogon = SUSER_SNAME()

SELECT SUSER_SNAME()

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

Dwain Camps SSC Guru Points: 86948 More actions · Answer 2

That's assuming you use domain creds to log into SQL Server (domain authentication), otherwise it returns the SQL Server login.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

sumith1andonly1 SSCrazy Points: 2250 More actions · Answer 3

hi

to get loigin user

DECLARE @UserLogon Char(128)

Set @UserLogon = SUSER_SNAME()

select @UserLogon

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

You should ORIGINAL_LOGIN() instead in case there is any impersonation going on.

http://technet.microsoft.com/en-us/library/ms189492.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 5

Thanks Sean - I will give that a try - we do use interpersonate - as its an ERP system with security within the GUI rather than at the SQL end.

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.