In this post ,will discuss logon Triggers in SQL Server. So when does a logon trigger fire? as the name implies logon triggers fire in response to a logon even. Logon triggers fired after the authentication phase of logging and finishes.
But before the user session is actually established, logon triggers can be used for tracking login and activity, restricting logging to SQL server, and limiting the number of sessions for a specific login.
Let’s understand these logon triggers with an example
What we want to do is write a trigger that’s going to limit the maximum number of open connections for a user to 4. If an attempt is made to make a 5th connection, then we should get an error message, as you can see here,
So it’s the logon trigger that’s blocking this attempt to make it 5th connection, let’s see, how to write?
CREATE TRIGGER tr_LogonRestrictConnectionTriggers ON ALL SERVER FOR LOGON AS BEGIN DECLARE @LoginName NVARCHAR(100) Set @LoginName = ORIGINAL_LOGIN() IF (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = @LoginName) > 4 BEGIN Print 'Third connection of ' + @LoginName + ' blocked' ROLLBACK END END
we will be making use of sys.dm_exec_sessions View to achieve this. Basically, sys.dm_exec_sessions View contains information about all active user connections. So if you look at view, it has got a lot of columns and we are interested in two columns. The first one is, is_user_process and second one is original_login_name.
So basically is_user_process column is going to tell us whether the connection is made using a user process. And the other column is original_login_name. Original_login_name column is going to contain the login name that made the connection.
And then I’m going to order data by login time and descending order so we will get the recent successful login information.
Now I can make 4 connections, but if I try to make a fifth connection, then we want to trigger, which is going to block that 5th connection.
So now, if you look at this trigger, what is this trigger doing?
It’s rolling back. So it’s preventing the attempt to make a fifth connection. And we are printing a message.
So where is this message, this error message going in? And it will actually be returned to the error log.
if you want to read the information from the error log, you can use this system stored procedure, sp_readerrorlog. So let’s go ahead and execute that system stored procedure.
Read More-
- DML triggers ,After Insert,After Delete Trigger with examples
- After Update Trigger in Sql Server With Example
- Instead Of Insert Triggers in Sql server With Example
- Instead Of Update Trigger In Sql Server With Example
The post Logon Trigger Example to Restrict Access in Sql Server appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Event nofication on QUEUE_ACTIVATION stops working after queue is disabled and enabled again
- Using two databases interchangeably with identical structures but different naming conventions
- trigger not updating sql
- Error attaching a database (.mdf file) to SQL Server
- ServiceStack OrmLite Not Retrieving SqlGeography Fields
- Setup service did not respond to the start or control request in a timely fashion error when installing SQL Server 2016
- How could my application on client communicate database on server
- How to simulate a situation when non-empty Transaction Log file will be added to the SQL Server Full Database backup file
- How to find the previous and current row difference
- Query System Views From Linked Server - SQL Server
- Reverse the order in the recursive query that gets all parents of an item
- Algorithm to get next weekday based on today
- How to create table in SQL Server using script code in Entity Framework?
- Trying to validate if a SQL Server data model uses temporal tables?
- IEnumerable<T>, Parallel.ForEach and Memory Management
- Replace single quotes in SQL Server
- Get Column names from a query without data
- TransactionScope/USEROPTIONS Isolation discrepancy
- SQL Select rows where two columns are equal, but a third is different
- Two ways to execute a Stored procedure in VBA, Which one is better?
- Check if starting characters of a string are alphabetical in T-SQL
- Use of ISNULL in a PIVOT Query in SQL Server 2008
- BULK INSERT tab-delimited file - unescape \x09
- Parse SQL Script to extract table and column names
- SQL Server sum from multiple tables in a single query
- SQL Server 2008 Full Text Search – SLOW
- How to ensure a precison on float using entity framework 6.x and a float column
- SQL - How to combine the results from a UNION
- SQL Server division query
- Connect to SQL database inside Script Task in SSIS
- Any other way to solve this query?
- full text view not return result
- Question about SQL Server Optmization Sub Query vs. Join
- Insert records from multiple rows of table to multiple columns of other table
- Function to check for NULL and concatenating the strings
- how to order list that has date and a word in sql server
- MAX vs Top 1 - which is better?
- Eliminating duplicates.. and more fun
- Is there a way to UPDATE TOP (N) with inner join where N is a field of such inner join?
- Are the elements of an XML alwas ordered if I get them from SQL Server 2012
- Concatenate Two Values On Insert - SQL
- SL error for using count(*) in join query
- Calculate difference between rows and keep the first row always 0?
- SQL Server, How to set auto increment after creating a table without data loss?
- SQLCMD passing in double quote to scripting variable
- Multiple languages in one database - SQL Server 2005
- Is it possible for me to use string formatting or equivalent in LINQ-to-SQL?
- SQL Server Using Select
- SQL Server Exlusive Row Lock Causing Table Select Blocking
- How to generate an access trail report in a single query
- SQL - how to GROUP by id and identify the column with highest value?
- Friendship Website Database Design
- based on SQL how to create a proper meaningful tables for stock inventory and for billing system?
- How do I add a record only if it doesn't already exist in SQL Server?
- Get date range between dates having only month and day every year
- SELECT * FROM USERS but not me
- How to get maximal dates period
- Group By clause not working correctly with sp_executesql
- How to append results of 2 SQL procedures from C# into a single CSV file?
- How adding some values in join works?
- SQL Server: How to determine if a value exists for a row
- Update a table upon insert into another table
- ExecuteNonQuery() breaks the loop and doesn't insert data in SQL database
- Exporting all tables into files from SQL Server database using utility bulk copy
- SQL - Audit log table design - Which would you prefer?
- JBoss AS 5 database connection pool re-connect routine for MS SQL Server
- How can you use COUNT() in a comparison in a SELECT CASE clause in Sql Server?
- Sql cursor loop taking a minute to loop through 20 rows
- If not CPU, disk or network, what is the bottleneck during query execution?
- SQL Server + ODBC + QT : Showing BLANK records after transaction
- Taking the Largest SUM from a table
- replacing data in SQL server
- SQL Server datetime2 in OPENQUERY
- How to insert three new rows for every result of a SELECT query into the same table
- How to fix invalid column name?
- How do you deal with blank spaces in column names in SQL Server?
- Upgrade from SQL Server 2008 to 2017 causes date subtraction error on Access front end form using DCount
- Inconsistent request timings in ASP.NET WebAPI and IIS
- How to solve conversion error in SQL Server?
- how to move data from one database to other without SSIS package
- Opening and Closing balance
- Square Bracket SQL
- How does setMaxResults(N) in Hibernate work?
- Error in if not exists query in SQL Server
- Set multiple "primary keys" on one AccountID
- Calculate hours spent in office
- Desktop Applications Using Java Connected with SQL database server
- SQL Server: Selecting 1 rows results in 1+3 rows affected?
- SQL Server different performance in a WHERE clause when constraint is hardcoded
- Match 2 where clauses in query
- Why is variable declared inside IF statement created even when condition evaluates to false?
- TSQL Help Without Cursor
- UPDATE takes long time on SQL Server
- Is a simple ASP.NET site using SQL Server known as 2-Tier Architecture?
- How to Generate Matching MD5 Hash: SQL Server nvarchar Field vs. ColdFusion
- removing all the characters except numbers
- SQL statement DateTime format
- Rolling Up Multiple Rows Into Single Row With Same Number Of Columns and Different Values
- SQL View returning different results when selected database different
- SQL: Unexpected results with SUM and COUNT functions
- How to prompt an SQL query user to input information
- A nicer way to write a CHECK CONSTRAINT that checks that exactly one value is not null
- Connecting to Microsoft SQL server with PHP
- T-SQL query group in date order (gaps and islands)
- available room booking between two dates and and two times column sql server
- Issue in reading Chinese language as XML input in sp_xml_preparedocument
- SQL Server safely cast string and fail silently
- CHAR function on SQL Server is returning different values on different servers
- Query Windows Version
- How to clear Query Messages?
- How to eliminate outer reference from subquery
- Create table with column names from another tables column data
- Different form view when different account type logs in
- Is a view faster than a simple query?
- Master user lost it's permissions unexpectedly on SQL Server (rds instance)
- Metadata database design
- Remove string after character in where statement
- Return queryable table from a stored procedure/function in SQL Server 2008
- Calling a stored procedure in MVC
- Make Procedure with SCOPE_IDENTITY()
- SQL 2012 + LIKE + SPRINTF
- Stored procedure - Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ''
- Update multiple column value from one column of the same table
- Can I set this column as the primary key?
- Android 4.1 SQL server connection
- Query to get the customers who haven't transacted in the past two months
- Syntax error near 0
- Access Denied to SQL Server (through Excel VBA) when using SQL User without Login
- How to combine data from two different sources
- Can't call correct value from dropdownlist ASP.net
- In SQL can I return a tables with a varying number of columns
- SQL Not Auto-incrementing
- SQL Server 2014 insert/update smalldatetime value with seconds
- How correctly execute a MSSQL stored procedure with parameters in Python
- SQL, how to test concurrent transactions
- Cross PDO driver compatible GROUP_CONCAT?
- Using SQL, how do I update rows, using their own values?
- Chinese characters showing as boxes in SQL Server 2005
- Parameter act the same as regular string
- Allow select while transation is running
- Re-attach SQL database to replace the original (instead of backup/restore)
- Passing a table expression to a table-parameterized function
- Which query performs better IF Exists (Query) vs IF( ResultCount > 0) in SQL Server
- Inserting unique value into database PGSQL/MSSQL
- dbml file - create database
- Does not contain query
- SSIS: Just started getting a "Key not valid for use in specified state." error on my scheduled SSIS package
- VS2013 Database Project without generating dll
- Entity Framework Code First will not set Identity to Yes
- Pagination in stored procedure sql server