Solved

SQL 2005 database - Trigger to return user name on network when using sa logon to database

Posted on 2013-05-26
13
417 Views
Last Modified: 2013-05-28
I have an application updating a MSSQL 2005 database and would like to record the change to  one field on a table. I have the trigger working fine to record this and can return all info I need except I would like to return the name of the user logged in on the network.

I have used USER_NAME - returns dbo and SYSTEM_USER - returns sa.

HOST_NAME() returns the Computer name which is useful.

I can get the @@SPID and lookup the relavent info from the session - sysprocesses - there is a field nt_user_name but this is blank and I cannot see anything that gives the logged on user.

Appreciate any help in identifying how I could access this.
0
Comment
Question by:donhannam
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 250 total points
ID: 39198611
As far as I know it's not possible to retrieve the Windows user ID unless you are using Windows authentication to access the database.
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39198612
As far as I know it's not possible to retrieve the Windows user ID unless you are using Windows authentication to access the database.
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39198613
As far as I know it's not possible to retrieve the Windows user ID unless you are using Windows authentication to access the database.
0
 
LVL 12

Expert Comment

by:Tony303
ID: 39198836
Am I right in thinking the app is using sa as the connection string? Not the best. Also, I agree with Kvwielink, windows authentication will be able to log the NT user via your trigger.
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39199178
Creating a windows login for every user in the sql server will be a problem,
both for maintenance of the logins (adding the new users and also deleting the old ones) and also the security purposes, what if you dont want the user himself dont want to have access to your database in any case (apart from your application), by creating a windows login on your server, you will be essentially given access rights to that user on your database (which might become an audit issue later).

Now, what are the options available in order to achieve this thing..


Almost all the applications, will have way to get the NT user id.
Now, get the user id in your application and pass it on to your database along with the other fields...
Just incase if you dont have the nt user name on your table itself then I suggest you to add it....
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39199834
Creating a windows login for every user in the sql server will be a problem,
You do not need to create a Windows Login in order to use Windows Authentication.  In fact, it is as you have pointed out a lousy idea.  A better approach is to create a Windows Group called something like SQLUsers, you can then give SQLUsers the right permissions.  Then it becomes a simple process of adding and subtracting users from this Windows Group and not from SQL Server.  In other words, once you have created the SQLUsers Login in SQL Server, it is no longer your (as a SQL Server DBA) problem, but rather the IT staff.  :)
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:donhannam
ID: 39199876
Thanks for the comments - sounds like the windows user ID is not directly available?. I can get the HOST_NAME - the computer that is being used - very rare 2 users will be logged on at once - is it possible to find the user who is logged on to a computer in the network/domain at the time the trigger is run?.

This is a third party application - It is set up with all users on the sa password and security handled in application. Would prefer to leave this as is and find a way to access the windows user from the trigger.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 39200051
Is the third party app using a connection string that you can modify?  If so there may be a workaround to set the user name.  Otherwise you are simply out of luck.
0
 

Author Closing Comment

by:donhannam
ID: 39203470
Thanks for comments - was hoping there was a way of getting this but looks like its not possible.
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39203524
Don't rule out Neo_jarvis' point about having the application pass the NT user name into the database. If this is really important for you, and you don't want to change the security setup for accessing the database, it might be best to ask the supplier of your application to add the NT user name to the database. This should not be too complicated.
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39203525
Don't rule out Neo_jarvis' point about having the application pass the NT user name into the database. If this is really important for you, and you don't want to change the security setup for accessing the database, it might be best to ask the supplier of your application to add the NT user name to the database. This should not be too complicated.
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39203526
Don't rule out Neo_jarvis' point about having the application pass the NT user name into the database. If this is really important for you, and you don't want to change the security setup for accessing the database, it might be best to ask the supplier of your application to add the NT user name to the database. This should not be too complicated.
0
 

Author Comment

by:donhannam
ID: 39203533
Thanks - unfortunately cannot get application supplier to do this
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query help 7 96
SQL Server Remove all trailing commas 10 71
SQL HELP 2 82
Need help creating a stored procedure 4 57
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now