2
There is a situation that I believe will be of great importance to me, which is to know the origin of an sql command in SQL SERVER.
Suppose that accidentally a user has executed any command. Then I need to know who executed such a command.
Is there any way to know who was the executor of such a command, the day and time and other information related to the command through sql command only?
1
Just enable the queries log.
Then I would do the queries in the log tables of sys.
Example:
SELECT
c.connect_time,
s.login_time,
s.host_name,
s.login_name
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
*The above query is merely illustrative. Adapt it to your need.
Obviously there must be a rigid hierarchy of access to the functions of the database. If you provide the same user for everyone to use, you’ll never know who executed what.
Usually user activities are controlled by the application. However, if there are users with permissions with direct access to the database, as there is no intermediate application, you will have to log using the log resource of the SGDB itself.
Blz, but is this independent of where these users are? For example, think of two cases. A case that are all in the same network, and another case that are each in their homes, for example. There will be differences?
This is from SGDB itself. The log of executions is saved in the tables of sys, regardless of the source of the execution. The location of the user is indifferent.
Daniel Omine, there was only one thing I didn’t understand in your query. That’s the part you can for me to adapt it according to my need. Where I would place the command I seek to know. For example, I want to know who gave a truncate yesterday, where I would put it there in the query you passed?
Look in the documentation. The example above basically shows the user name, date and time you executed and execution time, such as the host. Take a look at your database in the SYS tables to get other data you want to query.
I ran here and saw the information. In network they seem to me very useful, I will take a look at the documentation and test with those of the SYS table
Daniel, after my research, I checked this out today. https://sqlship.wordpress.com/2011/06/30/how-to-capture-deletand-truncate-t-sql-using-database-audit-specification/ You are familiar with this Audit?
Yes, they are sql server audit tools. Also useful for what you need.
Daniel I am to accept your answer, but clarify one thing: the ip in the form of the numbers: Ex.: 192.198.162.0, should not appear in the result of this query when you "s. host_name"?
The host name has nothing to do with the client’s host. It is merely the host name the user has connected to. For example %, localhost, ip local do server. Get it? It’s the host set in the connection string.
Ah yes, now it’s clearer! VLW!
0
SQL Server does not provide solution for this. There is Trace, but it only saves the information when requested.
The best way to audit data change in your database is via Trigger.
Browser other questions tagged sql sql-server ssms
You are not signed in. Login or sign up in order to post.
By the query log you may be able to know from which machine/ ip came the command ai vc identifies the suspect.
– rray
you can inform me what information I would take from this suspect?
– DiChrist
This may help: http://stackoverflow.com/a/27525859/2236741
– cantoni
I think the same ip, imagine if everyone uses the same admin login ... no use knowing which user was.
– rray
The SQL Server Audit functionality was released in SQL Server 2008. I was interested and was going to do a test, however, the SQL I have here is 2005. More information: http://bradmcgehee.com/2010/03/30/an-introduction-to-sql-server-2008-audit/
– cantoni
I’m looking at this link you sent, it’s exactly what I’m looking for, I just want to understand everything that was answered there.
– DiChrist