April 22, 2011 at 12:39 pm
Good day! I'm trying to figure out how to get a Windows domain account name when using a single SQL Server account to connect to the database.
I'm wanting to add a trigger to a table that logs what user made a change when they all connect with the same account.
For testing, I'm starting a session in SSMS with the single user ID/password we use to connect to the database. These all return the SQL account we use to connect, not my user ID:
SELECT USER_NAME()
SELECT CURRENT_USER
SELECT SUSER_NAME()
SELECT SYSTEM_USER
SELECT SESSION_USER
SELECT USER
SELECT Suser_Sname()
Any help would be appreciated
April 22, 2011 at 12:42 pm
Since they all use the same ID, you would be better served to trap the source IP. With the sourceIP, you can at least track back to the workstation that made the change.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 22, 2011 at 12:54 pm
They're not using all the same Windows IDs though, so I would want to know what Windows domain account made a change. Also, we are not on static IPs, so IPs would only be good for a little while.
So, my network user id is: WH\BObama
For security, we use a single SQL account to connect, so users don't have right to the database; that SQL Server account is NSA with password NSA.
I want the trigger to log BObama made the change, not NSA
April 22, 2011 at 1:32 pm
SpectralGhost (4/22/2011)
For security, we use a single SQL account to connect, so users don't have right to the database
Your choice surrounding database security with respect to your application is the problem here. If you trust a user to make a change to data through an application, but not directly in the database, that means your application is doing too much in the way of data integrity checks to protect your data. Are you using stored procedures to modify data or does the application issue embedded SQL? If you are doing all data access through stored procedures and your security model as well as your database schema are designed to protect your data then you should have no problem allowing users to connect directly to your database.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 22, 2011 at 1:58 pm
I'm not setting up a new system and it is incredibly large, so altering design is not an option at this point.
April 22, 2011 at 2:05 pm
SpectralGhost (4/22/2011)
They're not using all the same Windows IDs though, so I would want to know what Windows domain account made a change. Also, we are not on static IPs, so IPs would only be good for a little while.So, my network user id is: WH\BObama
For security, we use a single SQL account to connect, so users don't have right to the database; that SQL Server account is NSA with password NSA.
I want the trigger to log BObama made the change, not NSA
Yes, understood but you have a limitation presented in trapping that information because the login information is that SQL account that everybody uses.
Though you use DHCP, you can also trap the machinename in your trigger - thus save the IP and the machine name. Unless your application has some sort of login that must be used to get into the application - you don't have many options.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 23, 2011 at 1:15 pm
SpectralGhost (4/22/2011)
I'm not setting up a new system and it is incredibly large, so altering design is not an option at this point.
I understand the challenge you're facing, but as Jason and I both have alluded to, your options are limited unless you change your design.
This would still constitute a design change but sometimes it is less invasive than a change to the security model...what I have seen some people do is have the application pass the info of the person making the change to the database directly in the query. This puts the onus on the client app and is not as convenient as the implicit nature of using ORIGINAL_LOGIN() or SUSER_SNAME() because it's constantly there as an explicit piece of code to deal with and maintain...but it gets the job done.
If embedded SQL, something like:
UPDATE dbo.SomeTable
SET SomeColumn = 'abc',
SomeOtherColumn = 'xyz',
LastUpdatedBy = 'Person Performing Edit'
WHERE Id = 10 ;
If stored procs something like this:
EXEC dbo.UpdateSomeTable @Id = 10,
@SomeColumn = 'abc',
@SomeOtherColumn = 'xyz',
@LastUpdatedBy = 'Person Performing Edit' ;
Another option I have seen employed is to create a SQL login for each person using the app and then map the app login to the SQL login behind the scenes so that each person logging into the app is actually logging into the database with a login that "belongs" to them and thus they are uniquely identified in the database. It's more work for the application to map an app login to a SQL Server login and adds DBA work to create those SQL logins and manage the passwords but it will open things up for you to use ORIGINAL_LOGIN() or SUSER_SNAME() in the database layer.
Just some thoughts. It's a tough situation and something I try to deal with in design for this very reason...so I don't end up with few options when management introduces an auditing requirement.
Edit: remove LastUpdatedDate from update efforts, the DB can handle that with GETDATE()
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 25, 2011 at 5:43 am
Thanks for the assistance to the both of you! It's just going to be more work than originally intended.
April 25, 2011 at 11:04 am
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 25, 2011 at 3:12 pm
SQLRNNR (4/25/2011)
You're welcome.
Ditto. Good luck 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 26, 2011 at 12:31 pm
If your app. uses IE, might it be possible to use Kerberos to pass user credentials along from the workstation to your application?
Just a thought...
Rich
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply