• 0

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


Question

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.

10 answers to this question

Recommended Posts

  • 1
  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.

  • 0

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.

  • 0

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 :) 

  • 0
  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?

  • 0
  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?

 

 

  • 0

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.

  • 0
  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.

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.
  • Posts

    • Wow, and here I'm still happily using 1080p...
    • Added an extra filter to Fail2Ban.  I thought about just adding this to my existing aibots filter, but for the time being I'm keeping it separate because it's "possible" real humans may trigger this one so as long as it doesn't start filling my inbox I'd like to get notified about these so I can adjust it as necessary in the future. I'm still holding close to 10k unique IP addresses at any given time that have been banned via the "aibots" filter that looks for certain user agent strings of known AI scrapers.  However, I've been getting an increasing amount of traffic trying to scrape the site with sanitized user agent strings that just look like normal web browsers, however... Because I enabled authentication I can now see that they're racking up lots of 401 (unauthorized) responses in the Apache "access.log" file, but they're not triggering anything in the Apache "error.log" file, which is where failed attempts to log in would appear.  Basically, if an actual human tried to log in with an invalid username and password they don't immediately go into "access.log" as a 401, they go into "error.log" with a status message such as "user FOO not found".  The only way to trigger a 401 simply by visiting the site, as far as I'm aware, is to hit "Cancel" on the login prompt, or otherwise try to access files directly without properly authenticating. So, given the fact I'm getting a few thousand 401 errors a day from sanitized user agent strings that don't show up in "error.log", which means no attempt at logging in properly, I added another jail/filter set to Fail2Ban to immediately ban anybody who triggers a 401.  This feels a bit nuclear so I may need to adjust it in the future, but as far as I'm aware so far no real humans are being inconvenienced so all I'm doing is wasting the time of some AI scraper bots. Example log entry 61.170.149.70 - - [25/Jun/2025:20:01:04 -0400] "GET /content/mdwiki_en_all_maxi_2024-06/A/Neuroregeneration HTTP/1.1" 401 3287 "https://kiwix.marcusadams.me/content/mdwiki_en_all_maxi_2024-06/A/Neuroregeneration" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.43" Contents of /etc/fail2ban/filter.d/apache-401repeat.conf #Fail2Ban filter for bots and scrapers that try to access #files directly without entering credentials for apache2-auth #and therefore trigger lots of 401 errors without triggering #the apache-auth jail. # #Marcus Dean Adams [Definition] failregex = ^<HOST> .+\" 401 \d+ .*$ Contents of /etc/fail2ban/jail.d/apache-401repeat.local [apache-401repeat] enabled = true ignoreip = 10.1.1.1 port = 80,443 filter = apache-401repeat maxretry = 1 bantime = 672h findtime = 10m logpath = /var/log/apache2/access.log Oh, and all this traffic is AFTER I explicitly banned Alibaba's IP ranges that were absolutely blowing me up day and night. Observation; two of the IP addresses that have triggered this jail in the 30 or so minutes since I turned it on were owned by Microsoft.  Wonder if they're doing their own AI scraping/probing, or if that's just an Azure VM owned by somebody else.
    • We don't have hardly any 4k HDR content now... ESPN and other sports channels need to get with the program.
    • We hardly have any 4K HDR content now..why in the world is 16K important??? Someone tell ESPN that it's 2025!!!!
    • Imagine paying for Office 365 and getting a crappy web wrapper instead of a native application.
  • Recent Achievements

    • Rising Star
      Phillip0web went up a rank
      Rising Star
    • One Month Later
      Epaminombas earned a badge
      One Month Later
    • One Year In
      Bert Fershner earned a badge
      One Year In
    • Reacting Well
      ChrisOdinUK earned a badge
      Reacting Well
    • One Year In
      Steviant earned a badge
      One Year In
  • Popular Contributors

    1. 1
      +primortal
      552
    2. 2
      ATLien_0
      208
    3. 3
      +FloatingFatMan
      175
    4. 4
      Michael Scrip
      152
    5. 5
      Som
      139
  • Tell a friend

    Love Neowin? Tell a friend!