?
Solved

SQL Database trigger

Posted on 2016-08-09
11
Medium Priority
?
45 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
10 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 36

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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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 36

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 36

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

840 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