- Audit on SQL Server (Server Audit)
- How to Create an Audit to Monitor Job Creation, Modification, and Deletion in SQL Server
- How to create an Audit trigger to log object manipulation in SQL Server
- SQL Server - How to implement audit and control of logins (Logon Trigger)
- Monitoring DDL and DCL Operations Using SQL Server fn_trace_gettable
- Using SQL Server Standard Trace to Audit Events (fn_trace_gettable)
- SQL Server - Permissions and privileges audit trigger at database and instance level (GRANT and REVOKE)
- SQL Server - How to Monitor and Audit Data Changes in Tables Using Change Data Capture (CDC)
- SQL Server 2016 - How to “Time Travel” Using the Temporal Tables Feature
- SQL Server - How to use auditing to map actual required permissions on a user
- SQL Server - Trigger to prevent and prevent changes to tables
- SQL Server - How to Create a Data Change History for Your Tables (Audit Logs)
- SQL Server - How to Avoid Brute Force Attacks on Your Database
- SQL Server Security Checklist - An SP with more than 70 security items to validate your database.
- SQL Server - How to know the date of a user's last login
- SQL Server - How to Avoid and Protect Against Ransomware Attacks Like WannaCry on Your Database Server
- SQL Server – Beware of server role securityadmin! Using privilege elevation to become sysadmin
- SQL Server - How to Avoid SQL Injection? Stop using Dynamic Query as EXEC (@Query). Now.
- SQL Server - Understanding the Risks of TRUSTWORTHY Property Enabled in a Database
- SQL Server - Password Policies, Password Expiration, Mandatory Password Change, and Login Lockout after N attempts
Hello people,
All right with you ?
After A LOT of time without posting any technical articles, I am starting to take time again to bring in more cool scripts and articles that I would like to share with you.
In this post, I will demonstrate how to create a trigger to audit grant and remove events (GRANT and REVOKE) on objects, databases, user role changes (database roles and server roles), and direct permissions on databases. This need came through a demand that came to me because some users were losing access in a particular instance and had no control to identify who was removing and who granted those accesses.
Permissions History Table Creation Script
With the script below, you can create the table that will store all the history of permissions granted and removed in the instance.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
USE [Auditoria] GO IF (OBJECT_ID('dbo.Trace_Alteracao_Privilegios') IS NULL) BEGIN CREATE TABLE dbo.Trace_Alteracao_Privilegios ( Id_Evento BIGINT IDENTITY(1, 1) PRIMARY KEY, Ds_Evento VARCHAR(255), Dt_Evento DATETIME, Ds_Database VARCHAR(255), Ds_Schema VARCHAR(255), Ds_Objeto VARCHAR(255), Ds_Tipo_Objeto VARCHAR(255), Ds_Usuario VARCHAR(255), Ds_Comando VARCHAR(MAX), Evento XML ); GRANT INSERT ON dbo.Trace_Alteracao_Privilegios TO [public]; END |
Trigger creation script
With the script below, you will create the trigger that collects the permissions audit.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
USE [master] GO IF ((SELECT COUNT(*) FROM sys.server_triggers WHERE name = 'trgAudit_Privileges') > 0) DROP TRIGGER [trgAudit_Privileges] ON ALL SERVER GO CREATE TRIGGER trgAudit_Privileges ON ALL SERVER FOR DDL_SERVER_SECURITY_EVENTS, DDL_DATABASE_SECURITY_EVENTS AS BEGIN DECLARE @Ds_Evento NVARCHAR(255), @Ds_Schema NVARCHAR(255), @Ds_Database VARCHAR(255), @Ds_Objeto VARCHAR(255), @Ds_TipoObjeto VARCHAR(255), @Evento XML, @Ds_Comando VARCHAR(MAX); SELECT @Ds_Evento = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(max)'), @Ds_Schema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(max)'), @Ds_Objeto = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)'), @Ds_TipoObjeto = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(max)'), @Ds_Database = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(max)'), @Ds_Comando = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)'), @Evento = EVENTDATA() INSERT INTO Auditoria.dbo.Trace_Alteracao_Privilegios SELECT @Ds_Evento, GETDATE() AS Dt_Evento, @Ds_Database, @Ds_Schema, @Ds_Objeto, @Ds_TipoObjeto, SUSER_SNAME(), @Ds_Comando, @Evento END |
Results
Now I will demonstrate some examples of what this trigger can audit.
That's it folks!
A hug to you and until the next post.
Dirceu, good afternoon. I'm using your scripts in my test base and I'm having similar problems with Laércio's. I can even login to the environment, but I can't create a login anymore. I'm blocked by the trigger. I do not understand how this happens, since the trigger only select and insert. Do you have any idea why this is happening? Thanks.
Dirceu,
I managed to solve! I was not the first to do this bullshit and this link has the solution. It worked!
http://www.connectsql.com/2012/09/sql-server-disable-logon-trigger-using.html
Sorry for the inconvenience and thanks for your attention once again!
Big hug!
Sorry, Durceu. Commented on the wrong post!
I'm talking about your Logins Audit trigger. In it I made the poop.
And I can't log in either with "sa" or with any domain user. Only with a single public user that I left out of it. Good thing, otherwise the system would have stopped!
I'll owe you a lot if you help me out of this without having to reinstall SQL on the server.
Hello Dirceu!
I was testing this trigger and found a problem with a situation here in my environment, login with Crystal Reports. In an attempt to adjust this I set the CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS and ANSI_PADDING parameters to OFF and recreated the trigger. Result: I can no longer log in to the server! I can only with a public user that I left out of control, but with that I can no longer change or delete the trigger.
Do you have any suggestions to help me solve this trick I made?
Laercio,
Good day.
Did you try to log in with the user “sa” or any user who is sysadmin? I didn't understand why this trigger would cause this effect, because it only audits security events and not login.