?
Solved

SQL Database trigger

Posted on 2016-08-09
11
Medium Priority
?
43 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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 69

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

777 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