Monitor performed SQL SERVER 2014 Insert/delete/update

Question

Does anyone know how to register Insert/delete/update performed in the database outside the application or restricting by user?

For example only via Sqlmanager only,

The Database is SQL Server 2014;

Answer 1

Without using audit, only via Trigger. Using this Trigger creation model via SQL Dynamico, Voce can monitor actions.

Create your _LOG table first and add below.

DECLARE @tablename VARCHAR(100) 
DECLARE @audittable VARCHAR(100) 
DECLARE @sqlInsert VARCHAR(MAX) 
DECLARE @sqlColumns VARCHAR(MAX) 
DECLARE @sqlJoin VARCHAR(MAX) 
DECLARE @sqlWhere VARCHAR(MAX) 
DECLARE @sqlWhereFinal VARCHAR(MAX) 
DECLARE @sqlHeader VARCHAR(MAX) 
DECLARE @quote CHAR(1) 
SET @quote = CHAR(39) 

SET @tablename = 'SUA_TABELA'        --Replace this with the table name for which you want to write the update trigger 
SET @audittable = 'SUA_TABELA_LOG'        --Replace this with the audit table you want to insert the changed data 

--this is just the header info  for the trigger 
SET @sqlHeader = 'IF OBJECT_ID('+@quote+''+@tablename+'_U'+@quote+') IS NOT NULL 
       DROP TRIGGER dbo.'+@tablename+'_U 
GO 

CREATE TRIGGER dbo.'+@tablename+'_U 
ON dbo.'+@tablename+' FOR update 
/************************************************************** 
* Update trigger for '+@tablename+' 
* 
* MODIFICATIONS 
* 01/01/2000 xxx New 
**************************************************************/ 
AS ' 
PRINT @sqlHeader 

--select insert into 
SELECT @sqlInsert = COALESCE(@sqlInsert+' ,' , '') + name + CHAR(13)+ CHAR(9)  FROM sys.syscolumns WHERE OBJECT_NAME(id) = @tablename ORDER BY colid 
SET @sqlInsert = 'insert into dbo.'+@audittable+'('+CHAR(13) +CHAR(9)+@sqlInsert +')' 
PRINT @sqlInsert 

-- select col list 
SELECT @sqlColumns = COALESCE(@sqlColumns+' ,' , '') +'d.'+ name + CHAR(13) + CHAR(9) FROM sys.syscolumns WHERE OBJECT_NAME(id) = @tablename ORDER BY colid 
SET @sqlColumns = 'select '+CHAR(13) +CHAR(9)+ @sqlColumns  

--strip the last linebreak 
SET @sqlColumns = LEFT(@sqlColumns, (LEN(@sqlColumns)-2)) 
PRINT @sqlColumns 

--generate the join condition between Inserted and Deleted tables if the table has Primary key 
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE   WHERE table_name  = @tablename AND constraint_name LIKE '%PK%') 
BEGIN 
               SET @sqlJoin = '' 
               SELECT @sqlJoin = COALESCE(@sqlJoin , '') + 'd.'+ column_name + ' = i.'+ column_name + CHAR(13)+CHAR(9) +' and ' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_name = @tablename AND constraint_name LIKE '%PK%' 
               SET @sqlJoin = 'from ' + CHAR(13) + CHAR(9) + ' deleted d join inserted i on ' + @sqlJoin  
               --strip off the last 'and' 
               SET @sqlJoin = LEFT(@sqlJoin, (LEN(@sqlJoin)-6)) 
END 
ELSE 
       SET @sqlJoin = 'from deleted d, inserted i' 

PRINT @sqlJoin 

--generate the != clause where you check if atleast one column is changed... 
DECLARE @coltype VARCHAR(100) 
DECLARE @colname VARCHAR(100) 
SET @sqlWhereFinal = 'where'   

DECLARE colcursor CURSOR LOCAL FORWARD_ONLY  READ_ONLY FOR SELECT st.name, sc.name 
FROM sys.syscolumns sc JOIN sys.systypes st ON sc.xtype = st.xtype  
WHERE OBJECT_NAME(sc.id) = @tablename AND sc.name NOT IN 
(SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE   WHERE table_name  = @tablename AND constraint_name LIKE '%PK%') 

OPEN colcursor 
FETCH next FROM colcursor INTO @coltype , @colname 
WHILE @@fetch_status = 0 
BEGIN 
       SET @sqlWhere = '' 
       PRINT @sqlWhereFinal 
       SET @sqlWhereFinal         = '' 
       SET @sqlWhere = CASE WHEN @coltype IN('smalldatetime','datetime','sql_variant','ntext','varbinary','varchar','binary','char','timestamp','nvarchar','nchar','xml','sysname') 
                                               THEN  @sqlWhere + CHAR(9) + 'isnull(d.'+ @colname +','''') != isnull(i.'+ @colname + ','''') or'   
                                               ELSE 
                                                          @sqlWhere + CHAR(9) + 'isnull(d.'+ @colname +',-1) != isnull(i.'+ @colname + ',-1) or' 
                                               END 
       SET @sqlWhereFinal = @sqlWhereFinal + @sqlWhere  
       FETCH next FROM colcursor INTO @coltype , @colname 
END 
CLOSE colcursor 
DEALLOCATE colcursor 

--remove the last 'or' 
SET @sqlWhereFinal = LEFT(@sqlWhereFinal, (LEN(@sqlWhereFinal)-3)) 
PRINT @sqlWhereFinal

Answer 2

The change data capture (CDC) is an option; you had the opportunity to read the article SQL Server Security Audit Basics?

The subject is extensive to address in forum topic, but there are several articles on the web that deal with audit.

There are specific ready scripts for audit, that you can adapt to what you need.