Sql-server – How to distinguish the current operation is done from system or from sql server management studio

auditsql serverssms

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?

Best Answer

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.

Related Question