Trigger and create a simple logon Trigger

Trigger and create a simple logon Trigger

Introduction-

In order to connect using SSMS or client apps, SQL Server offers Windows and SQL authentication methods. For DBAs, security is always the first priority, particularly in the production setting. We can manage database access by giving users the minimal and suitable permissions for their task. SQL Server's provide and DENY statements can be used to provide each user the proper task permissions.

Trigger-

In SQL Server, a trigger is a special kind of stored procedure that is triggered automatically by specific events, such adding, modifying, or removing data from a table. Triggers can be used to audit data changes, uphold data integrity, and enforce business rules.

Types-

DML (Data Manipulation Language) and DDL (Data Definition Language) triggers are the two primary categories of triggers in SQL Server. DDL triggers are triggered by events connected to the database structure, including adding or removing tables, whereas DML triggers are triggered by insert, update, or delete statements.

 

Logon Trigger in SQL Server-

Database code known as "triggers" is run when a specific event occurs. Login triggers are a useful tool for managing SQL login security. When a logon event has place, SQL Server initiates the logon triggers automatically. It is carried out prior to the establishment of a user session and the accomplishment of authentication.

 

For an efficient database connection in any database, we use two terms.

 

Authentication-

To establish a connection using SQL authentication, we provide a username and password. It is an authentication process for the user.

 

Authorization-

Authorization is a permission that grants you the ability to carry out particular tasks. You might not be allowed to make any changes at the instance level, but you might be granted complete access to a particular database.

 

Create a simple Logon Trigger-

 

USE master;

GO

 

CREATE TABLE LogonAudit (

   LoginName nvarchar(128) NOT NULL,

   LoginTime datetime NOT NULL

);

GO

 

CREATE TRIGGER LogonAuditTrigger

ON ALL SERVER

FOR LOGON

AS

BEGIN

   INSERT INTO LogonAudit (LoginName, LoginTime)

   VALUES (ORIGINAL_LOGIN(), GETDATE());

END;

GO

In this example, we first create a table named "LogonAudit" to store the login audit information. The table has two columns: "LoginName" and "LoginTime".

Next, we create the logon trigger named "LogonAuditTrigger" those fires for every LOGON event. The trigger is created on the "ALL SERVER" scope, which means it applies to the entire SQL Server instance.

The trigger body is enclosed in the "BEGIN" and "END" keywords. It contains a single statement that inserts the login name and login time into the "LogonAudit" table.

The "ORIGINAL_LOGIN()" function returns the name of the original SQL Server login that initiated the session. The "GETDATE()" function returns the current date and time.

The "ORIGINAL_LOGIN()" function returns the name of the original SQL Server login that initiated the session. The "GETDATE()" function returns the current date and time.