Take Away

In this chapter, I have talked a great deal about creating a security plan and a security audit. You can combine these elements into a single document or once again store them in a database and report them from there. The security plan is a simple document that shows the protocols, services, and features (the surface) your system exposes to the world. The audit shows what you have done about those risks, and what their current state is.

In this section, I show you a few stored procedures that you can use to document your system, in addition to the ones I have explained throughout this chapter.

Security Auditing

I have not had you open your DBAMDT database in a chapter or so. The reason is that I do not want to give you lots of typing to do to create your system. Instead, I want to show you how to begin to create and fill out your own tables and create your own stored procedures to fill them. In each chapter, I show you a few more metadata mining procedures and views, and you can use the procedure I outline here to create your own monitoring system.

This chapter is tailor made for your security audit. As I mentioned at the beginning of the chapter, you really should keep a permanent record somewhere that you can deliver to others, but that does not preclude your storing the data electronically first. You can adapt the DBAMDT database to hold security data, too.

As I explained when we first created the monitoring database, you want to monitor some things less frequently than others. In fact, anything you can derive from a function, view, or system stored procedure that does not change does not have to be stored in the database. The reports that show the database can just run those queries to show the information.

But even if things change slowly, when they change you want a historical record of when it happened and what it changed to and from. The process to create this record is what we started at the beginning of the book, when we created the DBAMDT database. You will recall we created a table, a view, and a stored procedure to track the changes on a server.

To continue the process, all you need to do is to define what metadata you want to track, create a table for it, and then create any views, functions, and stored procedures you need to enter and display the data. You might create tables for file sizes, query activities, and other internal activities on the server. In this chapter, I have explained quite a few views and functions that have to do with security. In a moment, I list a few more.

To get the data from the system views and functions into your own tables, you first need a table to hold the data. You should design these tables to have the structure you plan to report on and any linking columns such as primary or foreign keys. For a view, you can use the INSERT INTO T-SQL command and a subselect to get what you want. I show you how to do that in a moment. For a stored procedure, the process differs a bit. Because you cannot select only certain columns from all stored procedures, you have to put everything from the stored procedure into a temporary table and then select what you want from there just as you do in a view.

Let's look at an example of getting information from a view into a permanent table. In this case, I have decided that I want to store information from the sys.sql_logins system view that shows the SQL Server logins on the server. I want to historically track the non-Windows accounts, but all I care about are the names of the accounts, when they were created, and their default database. The following script creates a table called SQLLogins in the DBAMDT database to hold the data:

USE DBAMDT
GO
CREATE TABLE [dbo].[SQLLogins](
[SQLLoginKey] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL,
[Created] [datetime] NOT NULL,
[DefaultDB] [varchar](55) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateRecorded] [datetime] NOT NULL CONSTRAINT
[DF_SQLLogins_DateRecorded] DEFAULT (getdate()),
CONSTRAINT [PK_SQLLogins] PRIMARY KEY CLUSTERED
(
[SQLLoginKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

I have created a column called DateRecorded that has a default value of a function that returns the current date (GEtdATE()). That provides the historical date I need for when the entry is created.

With the table all set, I can now use the INSERT statement to fill it with data. The basic format is the INSERT command followed by the table name and columns, and then the SELECT statement that retrieves the data. Here is my code:

USE DBAMDT
GO
INSERT dbo.SQLLogins (
 Name
, Created
, DefaultDB)
SELECT
 name
, create_date
, default_database_name
FROM sys.sql_logins
GO

This is just an example; you can use the same two-step process with any view or SELECT-accessible function. But what about those that are not, such as stored procedures that you cannot run a SELECT against? This example uses the xp_msver stored procedure to illustrate the stored procedure data mining process.

/* Look at the structure of the output from this
extended procedure: */
EXEC xp_msver
GO
/* Now use the output to create a temporary table with that
structure */
CREATE TABLE #ServerInfoHolder
(IndexNumber int
, Name varchar(55)
, Internal_Value varchar(55)
, Character_Value varchar(200)
)
/* And put the data from the stored procedure there.
This works the same way for a function. */
INSERT
INTO #ServerInfoHolder
EXEC xp_msver
/* Now you can look at the data in whatever format you want,
and use an INSERT statement to place it where you need it.
*/
SELECT Name, Character_Value
FROM #ServerInfoHolder
GO

There are other ways to do this, but the advantage here is that the data is available for many uses. You could "shred" out the columns into different insert operations and so forth.

This chart shows a few more security-related views and functions you can query for your base tables.

You can also build your own auditing by placing a DDL trigger on a table. Anytime someone modifies that table, you can have the code insert a row in your auditing table.