Skip to content

SqlSkull

SQL Server | SSRS | SSIS | Power BI | DAX
  • Home
  • About Us
  • Microsoft SQL Server
    • SQL Basics Concepts
    • SQL Advance Concepts
    • SQL Interview Q & A
    • SQL Server Tricks & Tips
  • SSRS
  • SSIS
  • POWER BI
  • Python with ML
  • Microsoft Azure
  • HTML Color picker

SqlSkull

SQL Server | SSRS | SSIS | Power BI | DAX
  • Home
  • About Us
  • Microsoft SQL Server
    • SQL Basics Concepts
    • SQL Advance Concepts
    • SQL Interview Q & A
    • SQL Server Tricks & Tips
  • SSRS
  • SSIS
  • POWER BI
  • Python with ML
  • Microsoft Azure
  • HTML Color picker
Home » SQL LOGON Trigger

SQL LOGON Trigger

  • by Pradeep Raturi
  • SQL Advance

LOGON Trigger fires in response to LOGON events of SQL Server is raised.





A LOGON Triggers can be useful in many ways, such as to restrict number of connection made by a Login or to prevent a user from logging-in outside .

For example, you can create a LOGON Trigger to restrict a user from simultaneously opening more than one connections with the SQL Server.

CREATE TRIGGER restrict_sqlsession
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN() <> 'sa'
AND
( SELECT COUNT(*)
FROM sys.dm_exec_sessions
WHERE Is_User_Process = 1 AND
Original_Login_Name = ORIGINAL_LOGIN()
) > 2
ROLLBACK
END

Now if user try to access more than one session in SQL Server, it returns an error as shown below.

 

Also Read..

DDL TRIGGER

LOGON Trigger




SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

 987 total views,  5 views today

Share this:

  • Click to share on Facebook (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on WhatsApp (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Related

Tags:create triggerfor logonhow to apply trigger on sql server tablelogon event in sqllogon event in SQL Serverlogon triggerlogon trigger in sqllogon trigger in sql serverLogon triggers in SQL ServerORIGINAL_LOGIN()restrict SQL Server SessionSQLSQL Databasesql demosql examplesql logon triggersql practicleSql QuerySQL Serversql server triggersql server triggersSQL Server Tutorials By Pradeep Raturisql statementsql triggerSQL TutorialSQL Tutorialssys.dm_exec_sessionst-sqlthe logon trigger in sql servertriggers in sqlwhat is logon trigger in sql serverwhat is LogOn triggers in SQL Server?what is trigger in sql serverxml

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

previousSQL DDL Trigger
nextHow to access a data of table from another database in SQL Server?
Generic selectors
Exact matches only
Search in title
Search in content

SQL Advance

  • SQL Advance
    • SQL Server Stored Procedure
    • How to Encrypt a Stored Procedure in SQL Server
    • Insert the output of a stored procedure into table/temp table
    • SQL Server Stored Procedures Vs User Defined Functions
    • SQL SERVER VIEW
    • SQL Server OUTPUT CLAUSE
    • SQL MERGE
    • SQL Server PIVOT
    • SQL Server Dynamic PIVOT
    • SQL Server UNPIVOT
    • SQL SERVER ROLLUP
    • SQL Server Synonyms
    • SQL Server CUBE
    • SQL Server GROUPING SETS
    • SQL SERVER User Defined Table Type and Table Valued Parameters
    • USER DEFINED FUNCTIONS
    • SQL Server Create SCHEMA
    • SQL Server Sequence
    • SQL Server OFFSET FETCH
    • SQL Server CTE
    • SQL Server Recursive CTE
    • SQL Server Change Data Capture
    • SQL Server TEMPORAL TABLE
    • SQL Server Cursor
    • SQL WAITFOR
    • IDENT_CURRENT Function
    • DROP IF EXISTS
    • SQL ERROR HANDLING WITH TRY..CATCH
    • SQL SERVER IF..ELSE
    • SQL GOTO Statement
    • SQL Server WHILE
    • SQL Return
    • SQL JSON_VALUE Function
    • SQL Server FOR JSON Clause
    • SQL JSON_QUERY Function
    • SQL JSON_MODIFY Function
    • SQL Server FOR XML Clause
    • SQL SERVER SET XACT_ABORT
    • SET STATISTICS IO
    • SQL SET Language
    • SQL SET ROWCOUNT
    • SQL SET NOCOUNT ON
    • SQL SERVER SET IDENTITY_INSERT
    • SQL SERVER SET DATEFORMAT
    • SQL CLUSTERED INDEX
    • Difference between clustered index and non clustered index
    • SQL Triggers
      • SQL DML AFTER TRIGGER
      • SQL DML Instead Of Trigger
      • SQL LOGON Trigger
      • SQL DDL Trigger
    • Get the definitions of Stored procedures, User Defined functions and Triggers in SQL
    • SQL ROLLBACK Transactions
    • SQL Server Dirty Reads in concurrent transactions
    • Lost update problem in concurrent transations
    • Identify Blocked Processes or Long Running Query In SQL Server
    • Schedule a Job in SQL
    • SQL sp_helptext
    • SQL Server sp_procoption startup procedures
    • SQL Server sp_executesql
    • SQL SERVER SP_MONITOR
    • SP_RENAME in SQL Server
    • SQL sp_help
    • How to Read SQL Server error logs using the xp_readerrorlog
    • SQL Server Database Mail Configurations
    • Send a SQL Server query results in e-mail message
    • Troubleshooting Database Mail in SQL Server
    • Import file in SQL server
    • Export data from SQL Server to an Excel file
    • Bulk Insert in SQL Server
    • Copy or delete files from directory in SQL Server
    • Row Level Security in SQL Server
    • Dynamic Data Masking in SQL Server
    • SQL Vulnerability Assessment
    • Column Level Encryption Decryption using Symmetric Keys
    • SQL Data Discovery and Classification
    • Script table in SQL Server
    • Generate Database Scripts With Data in SQL Server
  • SQL Basics Concepts
  • SQL Interview Q & A
  • SQL Server Tricks & Tips
  • SSRS-SQL Server Reporting Services

Page Views

  • 651,388 hits

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Translate this website..

Search for Tutorial

Generic selectors
Exact matches only
Search in title
Search in content
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Home
  • SQL Basics Concepts
  • SQL Advance Concepts
  • SQL Interview Q & A
  • SQL Server Tricks & Tips
  • SSRS-SQL Server Reporting Services
  • SSIS-SQL Server Integration Services
  • POWER BI
  • Python with Machine Learning
  • Microsoft Azure
  • HTML Color picker

SqlSkull. All Rights Reserved

 

Loading Comments...