SQL Server provides logon triggers that you can use to take specific actions on the database, when a user logs in. These triggers form part of a security plan for your server.
- [Instructor] In this video, we'll focus on logon triggers and their uses in SQL Server 2016. Now, essentially, logon triggers will cause a stored procedure to execute in response to a logon event. And, of course, a logon event is raised when a user session gets established with a specific instance of SQL Server. And the event that we are working with will fire after the authentication phase but prior to the session being established. So it's very important to understand why it works in that fashion.
So we authenticate the user and then we verify that the user is actually authenticated or allowed to perform the action that they want to do. If so, then the session gets established. However, if authentication fails, then the trigger's not going to fire. And that makes sense because if the user is not authorized to perform the action, then we don't want to allow them to perform the action at all. So the trigger won't fire and their authentication fails and they're not allowed to do whatever it was that they were looking for specifically.
Now, there's a couple of different ways of creating triggers. And even if you create a trigger from within SQL Server Management Studio, and we'll see this in just a minute, it will actually pop up a Transact-SQL template for you to complete. To simplify it for you, I brought up an example here where we can see we're using the master database and we're going to create a trigger called a connection_limit_trigger. What we're doing with this is we are saying ON ALL SERVER, so for the entire SQL Server, with the EXECUTE AS and a specific user, in this case TestUser1, for the action of LOGON, and then we create this store procedure.
So this is the AS BEGIN clause. And we say, "If the original login "is equal to TestUser1 AND." What we're doing now is we're actually executing a select statement from an exec_sessions system stored procedure to check and see if the user process is 1 and if the original login name of TestUser is greater than 3. So that's a long way around basically saying, "If we have more than three TestUser1 session logins, "then we're going to rollback." So we're not going to let any more than three TestUser accounts be logged on simultaneously.
So let's step out into SQL Server for just a second and we'll take a look at where we might find the triggers. We'll look at an example of one in the AdventureWorks database and then I'll show you that template that you will have to complete if you decide to create a new trigger. So in SQL Management Studio, we'll find our triggers in the individual databases that we have created. And they're typically assigned to the individual tables. So if we expand the HumanResource.Employee table, we can see a Triggers folder here.
If we expand that Triggers folder, we see that we have one trigger called dEmployee. Double clicking to open that shows that we have a Transact-SQL statement the creates that trigger. Now, we can run through and take a look at exactly what this is going to do. Note that it's simply saying that employees cannot be deleted. So there's some comments in here and we're executing some error message that gets sent back based on whatever action was taking place. Again, this is all Transact-SQL. We can also take a look at the current connection parameters specifically for this trigger that we have setup talking about the connection name that it will use, whether it's going to be encrypted or not.
But really the whole crux of what is taking place is all in the Transact-SQL trigger statement. If we decide that we want to create a new trigger, right-clicking the Triggers folder and choosing New trigger opens that template that I mention. So this is generated by SQL for you automatically. You can customize your comments here for when you created it. And then you're going to have to understand the syntax of the triggers to actually go through and complete the information that's in here. Again, these triggers can be set for a bunch of different actions within the database and within the tables.
And primarily, they're going to take place when a user logs on and attempts to execute something on that database. So just a quick overview of logon triggers within SQL Server.
This course is also ideal for anybody preparing for the Administering a SQL Database Infrastructure (70-764) exam, one of two exams necessary to earn an MCSA: SQL 2016 Database Administration certification.
- Installation and configuration considerations
- Data storage strategies
- Configuring database instances
- Performance considerations
- Security considerations
- Managing roles and users
- Managing data
- High-availability options