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
- Sql Server 2008 >> msdb.dbo.sysmail_sentitems
- SQL: Returning Nested values in an SQL query (SQLServer)
- can i have unique constrain on a NULLable fields?
- Cloud Run fully managed connecting to Cloud SQL: Does this support SQL Server?
- SQL query to retrieve last record from a linked table
- Save objects to a database?
- Create ODBC Connection from Access to SQL Server
- SQL Server issues when getting records starting from a date onwards
- Value cannot be null - How to get all entries of a database table
- Get highest count for specific rows
- Insert data to column which has two relationships
- Querying whole database with filtering tables and columns and there joins etc
- Get data from other databases
- How to parse xml tags with multiple xmlns attribute?
- migrating SQL Server 2005 instance with Biztalk databases
- How to upgrade SQLCE from 3.1 to 3.5
- SQL Server distinct query with two columns in same table
- Assigning Indexes to column/s?
- Is it possible to use multiple data sources inside an SSIS package under the OLE DB connection manager?
- Cannot Connect to Mssql database using PHP on shared Namecheap server
- SQL Native Client 10 Performance miserable (due to server-side cursors)
- GridView update function isn't working - does not update records
- How can I convert SQL datetime to DD/MM/YYYY 22:00
- Azure - Web to SQL with local (Azure) IP
- How to add loop of .bak files to restore in single sscript?
- ODBC Driver 18 for SQL Server]SSL Provider: [error:1416F086]
- SQL - Why values not selected?
- Making a data access class for all programs or individual C#
- SQL, only if matching all foreign key values to return the record?
- How to get Average Salary of SQL HourlyPayRate Column?
- Conversion of VARCHAR to DATETIME
- I want to concat some rows
- Custom mapping in Dapper
- The need of user with out log in SQL Server
- How to find amount exclusive of tax from an amount inclusive of tax?
- Why does child SSIS package's .dtsx file get opened in BIDS when running parent package in debug mode?
- How do I update a record by adding a constant to its current value in flask sqlachemy?
- Entity Framework 6.1.3 exec sp_executesql Causing Huge Performance Issue
- Strip the last dynamic column from ending comma?
- SQL - Limiting And Sorting Data
- Convert SQL query using 'count' clause, to LinQ
- Select all columns from all tables in SQL Server 2008
- How can I retrieve a Point from SQL Server with C#?
- SSIS performance in this case scenario
- SQL Query to Bring Back where Row Count is Greater than 1
- ms sql xml data type convert to text
- Is a view faster than a simple query?
- SQL: Subtraction Formula
- Avoid Unions to get TOP count
- VARCHAR(1) equivalent in C# land, SQL INSERT ERROR
- TSQL - INSERT ENTRY if particular entry does not exist within group
- How to fetch Male or Female from a SQL Server database table column and display the records in a repeater control?
- How to "HAVING COUNT(DISTINCT(col1, col2))"
- sum for duplicate rows without using group by
- ASP.net Razor - Sending email from a website using GMail SMTP
- Splitting a Column Using a Separator Character in SQL Server
- Is this connection string for SQL Server 2012 correct?
- how do i select the same row from one table for different values in another table + sql
- SQL SUM a CASE statement with an OVER clause
- Dynamic Google doughnut chart from sql query data
- find all tables not referenced in stored procedures
- How can I create named-instance of Microsoft SQL Server on Ubuntu
- Struggling with a pivot query in SQL Server
- Batch file to "Script" a Database
- Rolling sum based on date range in sql
- Multi Columns filtration in stored procedure in SQL Server
- Looping table valued parameter values passed as parameter to stored procedure for checking a value
- Recursive Function for MS SQL
- SQL Server - Simple multiple choice field definition
- SQL Server Agent Job Invoked
- How can I perform aggregate function logic across multiple fields?
- Display all available users on a given day
- Relations between Table and View using Microsoft SQL
- How to remove foreign key constraint in sql server?
- How to get a stored proc to reference a DB from outside its current execution scope
- send datepart as parameter from a table to DATEADD function in sql server
- Execute a SQL query/statement conditionally based on table data
- CAST/CONVERT empty string to INT in SQL Server
- Subquery returned more than 1 value. This is not permitted when the subquery
- What case is to be made when the Dividend and the Divisor have a value of zero?
- Why does the SqlServer optimizer get so confused with parameters?
- SqlException was unhandled by user code?
- SQL Server - view Nodes elements in Xquery
- How to fix - Unable to load DLL 'SqlServerSpatial110.dll' error
- Stored Procedure Timing out.. Drop, then Create and it's up again?
- Trouble reading data from sql database using java
- Keep getting 'cursor is READ-ONLY'
- SQL Server to PL/SQL
- SQL Server Recursive Hierarchy Query
- Get records that have all sub records
- Self join with aggregate function or some better way
- SSRS 2008 R2, VS 2008, User Defined Table Types
- SQL Server Switching Between IS NULL and = in same statement
- SQL server "Lock request time out period exceeded" .. again
- SQL Server 2012 Linked Server to MySQL Slow Select Query
- In SQL Server, is there a performance hit on 200+ columns in a table, or better way to manage it?
- Do I use inner join or left join?
- Find matching records of two different tables in SQL Server
- What is a Client Connection with User: None , Host: None , Name: compress_gtid_table in MySQL Workbench?
- Sequential numbers for many rows inserted with auto-increment key
- Get minimum and maximum decimal scale value
- How to use SQL to find second-highest auction bids
- SQL Server 2008 R2 Express: way to convert NULL's from Joins to zeros
- What advantages does a Document-based database have over a Relational database?
- Get the current month number in SQL Server, 1 for january, 12 for december
- VS2015 - SSIS custom component - bespoke form not showing, instead get Advanced Editor
- how to get this result
- Why does SQL Server say this is wrong?
- SELECT statement won't return row first time after INSERT commit transaction
- How can I implement MS SQL 2014 Stored Procedure
- Lower(field) = Upper(field)
- How to sort T-SQL query output in a custom order?
- Inner Join returning redundant data and not the expected data
- SQL - If two rows have the same date, only show row with certain column value
- SQL query select geography point
- Can SQLServer replication be limited to a specific set of tables in a database?
- How to select multiple rows in one column with a given condition
- SQL Server Automatic Rounding?
- Searching for data from one table in another table in t-sql (sql server2000)?
- Questions about increment operator
- How do I create a database programmatically in SQL Server?
- convert one row into columns
- Duplicate text values in dropdownlist, asp.net
- Clustered sorts data physically and non-clustered index too?
- Change nonth from 8 to 08 in SQL query
- hide duplicate row sql
- SQL Server looping execution takes too much time
- How I select the complete set of rows for each Sales Order by one common item?
- Entity Framework & SQL Server 2008 Express
- Find row that has same value in one column over multiple rows while another column has different values
- how to change this sql query
- SQL SUM Query returns duplicate rows in result
- Use average of values in "where" clause
- What is the reason behind maximum is 900 Byte in SQL Server database
- TSQL - Referencing a changed value from previous row
- T-SQL stored procedure, use variable as table reference in query
- Azure SQL Powershell , how to read a data view
- SQL Server: Compare Index Performance
- Passing multiple parameters into MDX query for WHERE clause
- How to reduce 'number of executions' in MS SQL Execution Plan
- Compare hash encoded passwords
- How do I change a subscriber schema?
- Autocomplete with Table Alias in FROM clause
- how long must a sql server connection be idle before it is closed by the connection pool?
- Cannot drop old domain user in SQL Server 2008 R2
- How to display database image (bytes[]) in mvc WebGrid
- SQL Issues - The multi-part identifier could not be bound
- Combine two tables and add missing values for missing ID
- What does a client need to connect remotely to an SQL Server?
- How to assign a sum of values within fields to a variable?