Logon Triggers in SQL Server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 96 of sequel silver
tutorial in this video well discus log
on triggers in sequel server so when
does a log on trigger fire as the name
implies log on triggers fire in response
to a logon event log on triggers fire
after the authentication phase of
logging and finishes but before the user
session is actually established log on
triggers can be used for tracking login
activity restricting logins to sequel
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 now is write
a trigger that's going to limit the
maximum number of open connections for a
user to 3 if an attempt is made to make
a fourth connection then we should get
an error message as you can see here and
look at what the error message says it
says logon failed for login it specifies
the login name due to trigger execution
so it's the logon trigger that's
blocking this attempt to make it 4th
connection let's see how to write such a
trigger so let's flip to sequel
management studio we'll be making use of
this view to achieve this basically this
view contains information about all
active user connections so if you look
at this view it has got a lot of columns
and we're interested in two columns the
first one is ease underscore use a
process so basically this column is
going to tell us whether if the
connection is made using a user process
and the other column is original login
name so this column is going to contain
the login name that made the connection
and then I'm going to order this data by
login time in descending order so we
will get the recent successful logins
information ok so if you look at the
data here notice that we've got you know
two sessions for the same user so this
is my user ID using which I have
connected to the sequel server now our
first question is why do we have two
sessions there because I only have one
query editor vendor open and look at
this query editor window the process ID
is 52 so we've got the process I
here 52 so this query editor window is
using that ID and if you look at you
know this first weight has got a
different ID 51 so this object Explorer
here on the left is using that ID so at
the moment I've got two connections now
I can make another connection but if I
try to make a fourth connection then we
want a trigger which is going to block
that fourth connection
so let's write the trigger so let's open
another query editor window so I am
going to create a trigger let's give
this trigger a name TR ordered login and
on all server 4 and we need to specify
the event so this is a logon trigger so
the event is going to be log on as begin
and and here we specify the body for our
trigger so we are going to make use of
this view in our trigger so I'm going to
make a copy of this and paste it within
the body and we are going to declare a
variable let's name it at login name so
this variable is going to contain the
login name and the data type is going to
be n we're care set at login name equal
so to get the login name that is attempt
to make a connection I am going to use
login underscore name I mean original
underscore login function so this
function is going to return as the login
that is you know making a connection and
that this variable is going to contain
the login ID so we are going to select
count of star
from the name of the view is six dot DM
hunts to exact underscore sessions where
is under school user let's actually copy
you know the column name from here these
two column names there is underscore
user underscore process equals one and
original login name equals whatever we
have in this variable okay
so this is going to give us the count of
connection so for example if I execute
this now at the moment notice that I
have three connections open you know one
is this one the other one is this one
and the third one is this object
Explorer window so if I actually execute
this query now for this login we should
have three open connections so 51 52 53
so this is 52 this query editor window
and this one is 53 this query editor
window and the object Explorer is 51 so
at the moment we've got you know three
open connections right so if that count
so let's use an if condition here so if
that count is greater than three then
what do we want to do we want to block
you know any more attempts to make a new
connection so I'm going to roll back
okay so this is going to prevent that
fourth connection and then it will
display an error message using a dialog
box like this and we want to print a
message to the error log so print fourth
connection attempt
by whatever is the login name so how do
we get the login name this variable has
got the login name login name blocked so
if I am trying to you know make a fourth
connection then within the error log
we'll have the information saying fourth
connection attempt by you know my login
name blocked you know so using that
error message we can see who are making
- who are trying to make more than three
connections okay so we have a simple
trigger here let's go ahead and create
this execute this command completed
successfully so this is a server scoped
trigger so you will find that in server
objects and within the triggers folder
so we have our trigger right there so at
the moment we already have three
connections now let's actually copy this
close this vendor and come right here
we'll paste our trigger code here now at
the moment I have only two connections
so when I execute this one we should see
that there are only two active
connections so if I try to open another
query editor window now which is going
to make a third connection that should
be allowed without any problem what is
that I don't get any problem now let's
try to open another one so I'm opening
another fourth one and look at this we
get the error message and look at what
the error message says it says logon
failed for login you know my login name
here are due to trigger execution and if
you look at the trigger that we have
here first of all notice this window it
says not connected so we are not able to
make that fourth 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 fourth
connection and here we are printing a
message so where is this print message
this error message going in it will
actually be written to the error log and
if you want to read the information from
the error log you can use this system
stored procedure SP underscore read
error log so let's go ahead and execute
that system store procedure so execute
SP underscore read underscore error
log and when we do that and when we
scroll all the way to the bottom we
should see that error message right
there look at this fourth connection
attempt by my login name blocked and
that's what is the error message that we
are writing to the error log fourth
connection attempt by this variable will
contain our login name and this word
blocked and that's what we see here okay
thank you for listening and have a great
day