Subtitles of the Movie

It is important to remember that when working with your databases, it's like any other computer program. They are multiple ways of accomplishing everything. We need to find what works best for us. In the prior video, I had shown how we could go to a database to come up with the system equivalent of the audit trail. I can do the same thing with using some simple lines of code in the Query Analyzer Screen. In this case, I'm just going to go ahead and type in Select Everything from System Databases. When I go ahead and execute the Command Line, you'll notice that it gives me the results showing me each of the database names and all the information going across. Now, notice this is a particularly difficult screen to work with. You may start recognizing some of the column headings though: Page Verification, Auto Create Statistics, Auto Update Statistics. This information is directly the same as what we just looked at. Let's go back to Northwind for instance. Do a right click and go into our Properties. In our Properties, we were under Option and what you are seeing are the same results that are on the screen: the Auto Close, the Create Statistics, whether it's True, whether it's False. The only difference when we go back into our screen is this has been converted to the binary or 0's and 1's. You have your choice of which way to go about it though. You can do a right click on that database or you can go straight into the query and use a select statement for that component. Now, there are other components that we can work with also. Let's say for instance, I'd like to find out who's actually in the Owner Role for a particular database. I'd like to find out all the individuals that may have that responsibility and I wanna know when they're logged in to the system. If I execute the first command, you will notice that immediately tells me the Owner Role Member. In this particular case, Lauri and the original default. What you'll notice within the coding is I'm simply telling it to use a particular database and then I'm going to select the name as the Owner Role Member. Now, I've asked it to go to this system database, particularly to the principals. Let's go into our Object Explorer for a moment. The areas that I'm referring to are going to be under the System Databases. If I go down to the System Views and then go down into the System Area, here's where you'll notice all of the files that we are referring to. In this particular case, I'm asking it to go over to the Database Role members. I'm just going to cross-reference the Role members and the Principals in order to come up with my information. Remember, your system databases are what's storing all the information about the current configuration. By putting in this simple statement, I can find out that information. These are the different types of audits that are available. Now, another example would be the same thing as to who the Login is mapped to. If I have user mapping set up within Security, then I can find out those components. It works the same way. I simply execute the command. When I execute the command, it would tell me who those log-ins are. In this particular case, other than just the actual default, there are none that have been actually assigned. And again, the key to remember is I'm going ahead and I'm going to name who is logged in. I'm going to refer to the system database principals to find out this information. When I'm looking for the owners, once again, I'm going to the system database principals to find this information but I'm also going to cross-reference that information to the database role members. By doing so, I can now actually create a simple query that gives me that information. So whether it be that we're more comfortable working with the Object Explorer and going into the individual databases or going into a select statement, I can find out the information from the audit trail that is happening right now within the database. As we go through these videos, the information that's contained here will become a bit clear. Remember, as with most tutorials, if this information is brand-new to you, I always recommend that you go through in its entirety first and then go back and catch some of the information that you might have missed at the beginning.