• 0

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


Question

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.

Link to post
Share on other sites

10 answers to this question

Recommended Posts

  • 1
adrynalyne
33 minutes ago, 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.

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

  • Like 1
Link to post
Share on other sites
  • 0
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.

Link to post
Share on other sites
  • 0
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.

  • Like 1
Link to post
Share on other sites
  • 0
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 :) 

Link to post
Share on other sites
  • 0
adrynalyne
7 minutes ago, 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 :) 

Do you have records of the users in the database?

Do you have access to the software code?

Link to post
Share on other sites
  • 0
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? :/ 

Link to post
Share on other sites
  • 0
adrynalyne
9 minutes ago, 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? :/ 

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?

 

 

Link to post
Share on other sites
  • 0
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.

Link to post
Share on other sites
  • 0
adrynalyne
13 minutes ago, 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.

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.

  • Like 1
Link to post
Share on other sites
  • 0
tetsu-kun

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

Regards :) 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.