1

We want to record who insert or update a table, so we add "CreatedBy" and "ModifiedBy" columns.

When the data is changed through the application, we can easily get the person. But when someone uses SQL Server Management Studio, how can I get the right person who operated on it?

Can I use trigger to catch it?

2
  • Do you mean they log onto the server, use Management Studio locally, and perform their updates using SQL authentication? Or are they using Windows authentication? And where exactly are they running Management Studio? More details > less details. Commented Jul 30, 2013 at 14:06
  • Hi Aaron, in all situations you said. Commented Jul 30, 2013 at 14:09

1 Answer 1

2

In most cases, you should be able to use some of the built-in functions:

SELECT @ModifyUser = SUSER_SNAME();

SELECT @ModifyUser = ORIGINAL_LOGIN();

This will work if they are Management Studio on their own machine and running queries remotely. If they are all using the same SQL authentication login, then in that case you should be able to tell them apart by grabbing the host name or IP address (then you'll have to figure it out based on that):

SELECT c.client_net_address, s.[host_name]
  FROM sys.dm_exec_connections AS c
  INNER JOIN sys.dm_exec_sessions AS s
  ON c.session_id = s.session_id
  WHERE s.session_id = @@SPID
  AND c.session_id = @@SPID;

In some cases, you're not going to be able to tell who it is. For example, if you let everyone log onto a common machine, access the instance through a local copy of SSMS, and they all use the same SQL authentication login, they will all look the same. There is no magic that will give SQL Server information it doesn't have. If auditing is important to you, there's a simple solution: stop letting them do that.

5
  • Thanks, Aaron. I think you're right. I tried several times, when people use SQL authentication login as "sa", I really can't get the client information I want. But if they use Windows authentication, I can get it. Thanks again! Commented Jul 30, 2013 at 14:41
  • 3
    @user26531 I'm going to put this in caps because it is very important: NONE OF YOUR USERS SHOULD BE USING sa. Give them their own accounts. Now. Commented Jul 30, 2013 at 14:42
  • Yes, this is very important. Commented Jul 30, 2013 at 14:48
  • At a former employer we had to get into compliance quickly. The decision was made to change sa's password. It was an interesting political situation because those who were using sa were out of compliance and didn't want to stick out their necks too far, but they also didn't know how to get some work done.
    – swasheck
    Commented Jul 30, 2013 at 14:52
  • Letting users login as SA is basically the same as owning a store and letting all your customers put their own money into the register and make their own change. Sure it will probably work OK for a while, but eventually someone will take something and you have no idea who it is.
    – JNK
    Commented Jul 30, 2013 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.