[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Database trigger

Posted on 2016-08-09
11
Medium Priority
?
44 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 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 35

Assisted Solution

by:ste5an
ste5an earned 1600 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
Looking for the Wi-Fi vendor that's right for you?

We know how difficult it can be to evaluate Wi-Fi vendors, so we created this helpful Wi-Fi Buyer's Guide to help you find the Wi-Fi vendor that's right for your business! Download the guide and get started on our checklist today!

 
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 35

Accepted Solution

by:
ste5an earned 1600 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
 
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 35

Assisted Solution

by:ste5an
ste5an earned 1600 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 70

Expert Comment

by:Scott Pletcher
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

656 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