How to know the user who made the move - SQL Server 12

[tetsu-kun]

Hi, as the title say it.

I have a program that use login with every user to access at every module ant this works with SQL Server 12. Until here everything is ok, but when I want to know witch user made de move (Insert, update or delete an record) I don't know how to know it.
Is there a way in SQL Server to do this function? The important to me is know it, even if in the system doesn't show it while in the DB I can consult, thats works for me.

Thx.

Regards.

[adrynalyne]

  On 06/11/2020 at 18:50, tetsu-kun said:

Hi, as the title say it.

I have a program that use login with every user to access at every module ant this works with SQL Server 12. Until here everything is ok, but when I want to know witch user made de move (Insert, update or delete an record) I don't know how to know it.
Is there a way in SQL Server to do this function? The important to me is know it, even if in the system doesn't show it while in the DB I can consult, thats works for me.

Thx.

Regards.

Expand  

Create an Audit table in SQL Server. Every operation, log who did it with timestamp. Record which table,  and previous and current value.

[tetsu-kun]

Hi @adrynalyne thanks for your reply

I'm searching how to do it with my database now  I don't know about that.

Regards.

[adrynalyne]

You can do it a few ways.

 

If you don't have access to the to software code, an easy way is to create a trigger on the database table so that it makes a record whenever someone does something.

[tetsu-kun]

The problem is I have a lot of tables that the users modify, and I want to know in some them who makes the moves, for example on my DB  dbo.INVENTORIES I want to know who does the I/O of the material, when and the type of movement (insert, update or delete; depending of the action).

Thanks for your time.

Regards  

[adrynalyne]

  On 06/11/2020 at 20:26, tetsu-kun said:

The problem is I have a lot of tables that the users modify, and I want to know in some them who makes the moves, for example on my DB  dbo.INVENTORIES I want to know who does the I/O of the material, when and the type of movement (insert, update or delete; depending of the action).

Thanks for your time.

Regards  

Expand  

Do you have records of the users in the database?

Do you have access to the software code?

[tetsu-kun]

There are records on every table, but the users only login on the modules with their username and pass, and I don't have access to the SW  code for that reason I try to do it on the DB

Is this possible?  

[adrynalyne]

  On 06/11/2020 at 20:41, tetsu-kun said:

There are records on every table, but the users only login on the modules with their username and pass, and I don't have access to the SW  code for that reason I try to do it on the DB

Is this possible?  

Expand  

Ah, thats a tricky one. Easy to audit. Hard to tag who it is.

 

Does the application connect to an API or directly to DB? If it connects directly to DB, do they have individual SQL accounts?

 

 

[tetsu-kun]

They don't connect directly on SQL they use an SW to log in and enter at individual modules to operate, but that's movements affect the DB.

 

     DB

 SERVER

      ↑

modules

      ↑

     SW

      ↑

   log in

 

That's the example to the access at the SW.

[adrynalyne]

  On 06/11/2020 at 21:05, tetsu-kun said:

They don't connect directly on SQL they use an SW to log in and enter at individual modules to operate, but that's movements affect the DB.

 

     DB

 SERVER

      ↑

modules

      ↑

     SW

      ↑

   log in

 

That's the example to the access at the SW.

Expand  

Ok, so the modules are in between.

 

I assume you also don't have access to the module software?

 

I'm not sure you will be able to do it unless the user signed in is being sent in some way to SQL Server, unless you can modify the module software.

[tetsu-kun]

I have access to the modules, but not at the code of it  thank you so much indeed for your time  

Regards