Subtitles of the Movie

There are a few ways that we can work with our audit within SQL. There are some tools that are built right into the Server Management Studio and then as always we can work with some actual query statements that will help us work with it. Let's start first by looking at the built-in features. I'm going to the Server itself and I'm going to Properties. When I'm in Properties for the Server, I'm going to the Security. The first thing you'll notice here right in the center is your login auditing. I can choose to track where there are attempts that have failed, there are successful attempts, or if it's both. This is entire up to you. Many times we like to track when employees are actually logging into the database. This is for the purpose of knowing that nobody's in the system in the middle of the night when we're doing our backups. Those are the kind of components where, tracking the successful logins come in handy. However, remember of course, that's going to be a lot more draw in your resources. Now once we set this on the server, we can then go in to our current logs, open up the logs and see what type of events are taking place. Notice it's telling me that the database is starting up, that server is being terminated and it cross-references the Process ID. Now if I close out of the log and I simply go over to the Activity Monitor, you'll notice that this will cross-reference the Process ID and let me know the user that is involved in it. When you setup audits, one of the things that is most important is that somebody must monitor the activity. Very often we'll find that security has been setup but nothing is being done with it. It's really important that one person be responsible for monitoring it. Now there are other things that we can do with audits with SQL. I'm going to hide the Object Explorer for this example. Another option is that I can actually create a table. This table, in this particular case, has been established to track any changes that are going to occur within the database itself. Now once I have the table, then I can actually establish a Trigger. The idea is the Trigger is going to look at what is happening within the actual database itself. So if tables are being created, or functions, if things are being dropped, I'm going to actually insert notification into a ChangeLog table. After I've setup the table and the Trigger, all that's left to do is to actually select the information from the table that we just created. Now I recommend that you do a little research and find out what method of working with these tables and Triggers is going to work for you. Microsoft's development side has lots of ideas on how you can use coding to your advantage in the auditing. I'm only trying to give you an example of some of the components that you can work with. It's very important to do your research and choose the method that works best in your environment.