Identifying NT User Name when Application uses SQL ID

Question

Post reply

Identifying NT User Name when Application uses SQL ID

Ross McMicken

SSCarpal Tunnel

Points: 4435
More actions
September 1, 2006 at 3:32 pm

#170235

We have an application that uses a SQL only ID to run all of the database activity. We want to know the actual NT user name rather than the SQL login. The NT user name is not consistently available. We want to write triggers on certain tables to track changes to critical data. Any idea on how to identify the NT user name behind a SQL login?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

Create a NT User Group

Assign the application users to the NT User Group

Create login in the SQL for the NT User Group and grant access to the DB

Grant execute on the sps in the database.

Then u can trace the users in the triggers.

Thanks

Sreejith

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

You can fool around with user names, if you want, but those can always be tricked especially when users make the mistake of sharing logins...

If your Windows network is setup properly, you know who "owns" a machine and using HOST_NAME() will ALWAYS give you the correct machine name no matter how someone logged in. And, if you've mapped where-in-the-building a machine is located, you can qucikly hunt down the offending person before they even know what's happening even if they log into someone else's machine on a "stolen" login.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ross McMicken SSCarpal Tunnel Points: 4435 More actions · Answer 3

We can't use groups as the ID's are sa equivalents and we don't want users to have that kind of access. The app reads the encrypted SQL ID and password from a table, then connects with that ID. The SQL used to manipulate data is in another database as parameterized queries. Not the best design in the world, but we are stuck with it because it's a good app otherwise, and there's nothing better for what it does.

We capture the host name now, but that means someone has to query active directory to discover the user id. We don't have to worry about users sharing ID's as most of them are on smart cards, and the ones that aren't face termination for ID sharing, which happens several times per year. Where host name breaks down is with users that connect via terminal server from remote locations (around the world). The host name is identical for several dozen users.

When I use Profiler on a test server, I can see my user id even when I'm connected as the SQL ID. I saw a mention one time that this was due to named pipes, but can't find any additional detail.