There is a situation that I believe will be of great importance to me, which is to know the origin of a sql command in SQL SERVER.
Suppose that a user accidentally ran any command. So I need to know who ran the command.
Is there any way to know who was the executor of such a command, the day and time, and other information regarding the command through sql command alone?
Just turn on logging queries.
Then you would query 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. Tailor it to fit your need.
Obviously there must be a rigid hierarchy of access to database functions. If you provide the same user for everyone to use, you'll never know who did what.
Usually user activities are controlled by the application. However, if there are users with permissions with direct access to the database, since there is no intermediary application, you will have to log in using the SGDB logging feature.
SQL Server does not provide a solution to 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.