Solved

SQL Database trigger

Posted on 2016-08-09
11
32 Views
Last Modified: 2016-08-30
Hello can I create a trigger the records user, Database, TranType, commandExec

User= User that is executing command
Database= the database where this command is being executed
TranType = Type of command (Select,Update, Delete, Create, Drop, Alter, Truncate)
commandExec = The actual command that is being executed.


I like to get this data so i can insert to a table to keep a log.
0
Comment
Question by:Leo Torres
11 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points (awarded by participants)
ID: 41748796
I think you should not do that via trigger, but using the sql profiler:
https://msdn.microsoft.com/en-us/library/ms181091.aspx
you want to configure the trace to run all the time, and save it to a trace table
however, you should really watch out on what exactly you want to trace, and how fast this table may fill up, and how to evacuate old data etc ...

explanation: SELECT requests cannot be caught by a trigger.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 41748824
We want this up for a couple of months to analyze who is doing what to the server so we can make an informed decisions on what is the security needed on the server. My fear is with a trace the file will become huge over a 2 month period. I am willing to let the select slide if I cant capture that in a trigger.

Your thoughts on trace size for 2 month trace? Good Idea or not?
0
 
LVL 32

Assisted Solution

by:ste5an
ste5an earned 400 total points (awarded by participants)
ID: 41748833
Well, imho the wrong approach. Security measures are for a reason, thus top-down. Not by usage.

But you should make yourself familiar with the audit features of SQL Server:

SQL Server Audit (Database Engine)
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 41748857
We have this DB in simple Mode as I understand it the database has to be in Full Mode to take advantage of these Audit Features. This is by no means a permanent solutions. I dont want to change the security on a production sever until I understand who is doing what on that server. Thats why I want to add a trigger for some time to give me any easy way to quickly analyze who is doing what so decisions can be made. We did discuss doing a trace but running a trace for 2 Month may degrade the server more than a trigger.
0
 
LVL 32

Accepted Solution

by:
ste5an earned 400 total points (awarded by participants)
ID: 41748893
"Thats why I want to add a trigger for some time to give me any easy way to quickly analyze who is doing what"

Forget about that "quick". Security analysis is one of those hard tasks. There is no quick way.

But on the other hand: Just store the query plans in a regular basis. This is normally sufficient to determine the "what".

SELECT --TOP 100
        GETDATE() AS snapshot_datetime ,
        RANK() OVER ( ORDER BY total_logical_reads + total_logical_writes DESC, sql_handle, statement_start_offset ) AS row_no ,
        creation_time ,
        last_execution_time ,
        total_worker_time / 1000 AS total_worker_time ,
        total_logical_reads ,
        total_logical_writes ,
        execution_count ,
        total_logical_reads + total_logical_writes AS total_logical ,
        CASE WHEN sql_handle IS NULL THEN ' '
             ELSE ( SUBSTRING(st.text, ( qs.statement_start_offset + 2 ) / 2,
                              ( CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
                                     ELSE qs.statement_end_offset
                                END - qs.statement_start_offset ) / 2) )
        END AS query_text ,
        st.text AS sql_text ,
        DB_NAME(st.dbid) AS database_name ,
        st.objectid AS object_id ,
        qp.query_plan ,
        cp.cacheobjtype ,
        cp.objtype ,
        qs.plan_handle
FROM    sys.dm_exec_query_stats qs
        INNER JOIN sys.dm_exec_cached_plans cp ON cp.plan_handle = qs.plan_handle
        CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
        CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE   total_logical_reads + total_logical_writes > 0
ORDER BY total_logical DESC;

Open in new window

0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 8

Author Comment

by:Leo Torres
ID: 41748909
Agreed security is not easy but it is made impossible if you don't have factual information to make decisions. your query is similar to what I need but I need to know "WHO" is executing these queries. Just the fact that they are being executed does not help me.
0
 
LVL 32

Assisted Solution

by:ste5an
ste5an earned 400 total points (awarded by participants)
ID: 41748936
Well, then you're back at the audit features.

Here is a nice overview of your possibilities: SQL Server database auditing techniques.

Imho the key is really a top-down architecture:
Implement SQL Server Audit. Take the requirements and the information from the query plans and design a security system. Review it. Then implement your system. You'll get immediate response what is not working like "indented".

Cause implementing security in an existing system will always result in some broken access vectors.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 41748941
Some Broken access is fine. There are some access that's has to be removed. Which is exactly why we are doing this exercise. But we are not moving the DB to Full Mode.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41749077
Typically Extended Events are the least overhead, so if they will do what you need, I'd try that first.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 41750951
Never used Extended Events do you know a good link to read up?
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now