Get OS Username in Trigger

Question

Get OS Username in Trigger

imran.baig

Old Hand

Points: 317
More actions
April 16, 2014 at 2:04 am

#284663

Dear All,
User is login to the application using SQL Server Autentication method. I want to get his windows OS username to log in one of my security trigger.
All of the following:
SUSER_NAME(), SYSTEM_USER AS OSUser, SUSER_SNAME() AS SuserSName, ORIGINAL_LOGIN() As OriginalLogin, USER_NAME() as UserName
Are returning database user name, while i need OS username.
Kindly help
imran

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

If he's logged in with SQL authentication, the SQL username is all that you can get. If you want to get the windows logins, require people to use windows authentication.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

Agreed with Gail. You are using SQL login and want the info of windows login? There is no logic.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

imran.baig Old Hand Points: 317 More actions · Answer 3

Thanks for your reply.

Is there any work around for this. Because we are using SQL Server autehntication in applications connectivity. And we have application level users.

For security reasons it is required to store windows's login user.

Regards, Imran

Lowell SSC Guru Points: 323506 More actions · Answer 4

my network guy was able to provide me with a list of user accounts + last workstation they logged in on;

I stuck that in a table, and use that as an auxilliary table and join it against hostname() to see when , say workstation42 logged in with a SQL account, and i KNOW mydomain\william uses workstation42.

then i can email him to tell him to stick with the

best practice of logging in as himself, and not the SQL login he culled from the application.

i know it's not perfect, since hostname and application name can be spoofed, but it's a 90%+ solution for me to track SQL logins and domain users.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

imran.baig Old Hand Points: 317 More actions · Answer 5

But what if 2 users are using the same machine with their own user and password? It may be difficult to track in an environment with many users and when users also have a habbit to login from different machines.

Lowell SSC Guru Points: 323506 More actions · Answer 6

imran.baig (4/16/2014)
But what if 2 users are using the same machine with their own user and password? It may be difficult to track in an environment with many users and when users also have a habbit to login from different machines.

yep that's where you cannot know for sure who the user is.

the right solution? disable SQL logins and force them to use AD accounts, but i know in many, many situations that's just not possible.

I'm weaning my own developers off of SQL logins right now.

if you control the application they are using, then you could stuff their network username in CONTEXT_INFO(), which you can then read form the dmv's on a per connection basis.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!