programming4us
programming4us
DATABASE

SQL Server 2008 : Creating SQL Server Audits Using the GUI

2/27/2011 10:51:25 AM
Since you know how to create SQL Server audits and add server and database audit specifications using T-SQL, we would like to spend a brief moment on the creation of SQL Server audits from SQL Server Management Studio. We are going to create a database audit specification, since you can include database-level audit action groups and a database-level audit action. Remember that database-level audit action groups are the predefined action groups created for you by SQL Server, while you get to define the database-level audit actions. As you know, server audit specifications only use server-level audit action groups, which are similar to the database-level audit action groups.

We are going to follow the same steps used in T-SQL: create an SQL Server audit, create a database specification, add database-level audit action groups, and then database-level audit actions to the database specification. To create a SQL Server audit, connect to an instance of SQL Server using SQL Server Management Studio. Once connected, navigate to the Security folder, expand the folder, then right-click on the Audits folder, and select New Audit. Figure 1 shows the options available in the GUI.

Figure 1. General page of the Create Audit dialog box

First, name your audit. Then set the queue delay—remember, the measurement is in milliseconds. Select the check box next to Shut Down Server on Audit Log Failure if you want to shut down the SQL Server instance when the audit is unable to log data to the target. After that, determine the audit destination. If you choose File, then you have decide the file path, maximum file size, maximum rollover, and if you want to reserve disk space. Once you populate all of the values, click OK to create the audit.

1. Creating a Database Audit Specification Using the GUI

After creating the audit, navigate to the database where you want to create the database audit specification, drill down to the Security folder, right-click on the database audit specification, and then select New Database Audit Specification. See Figure 2 to review the available options in the Create Database Audit Specification dialog box.

Figure 2. The Create Database Audit Specification dialog

First, name the database audit specification and select the audit that you want to associate the database specification to. After that, notice that you have four selectable options to create actions for the database specification: Audit Action type, Object Class, Object Name, and Principal Name.

In the first column, the Audit Action Type drop-down list contains the database-level audit action groups and the database-level audit actions. If you select a database-level audit action group, then you do not have any other selectable options to choose from. The action groups we previously discussed already have their actions defined. Choosing a database-level audit action gives you the flexibility to define exactly what you want audited.

The Object Class drop-down list contains three options: Schema, Database, and Object. In other words, do you want to audit the audit action for a database, a particular schema, or for a particular object? The object class you select will determine the types of object names that are available. Choose the object class that you want audited for the audit action type you selected.

The Object Names ellipses will contain all of the object names for the object class selected. For example, if you select Database, then the Object Name drop-down will list all of the available databases. Schemas and objects work similarly. Identify the object name that you want audited and select the name from the list.

After you select the object name, then select the principal name that you want to monitor. When you click on the ellipses, the list displays available principals. Choose the principal name that you want to audit from the list. Notice, you can even select database roles from the list. After you populate the options, click OK and create the database audit specification.

After creating the database audit specification, navigate to the recently created database audit specification, right-click on it, and select Enable to enable the database audit specification. You also need to enable the audit. So navigate to the audit that you associated the database audit specification with, right-click on the audit, and select Enable. Now both the database audit specification and the audit that it is associated with are enabled.

2. Reviewing Audit Files Using SQL Server Management Studio

We have demonstrated querying a file to review the audit information in a file, but we want to show you how to review the audit information from the GUI. Navigate to the Audits folder under the Server Security folder. Right-click on the audit that you want to review and select View Audit Logs. As shown in Figure 3, you can select multiple audits. That can prove helpful if you are trying to correlate when events occurred from multiple audits.

Figure 3. Results of a salaryViewing audit using SQL Server Management Studio


Other  
  •  SQL Server 2008 : Creating Database Audit Specifications
  •  Programming Microsoft SQL Server 2005 : The XML Data Type (part 3) - XML Indexes
  •  Programming Microsoft SQL Server 2005 : The XML Data Type (part 2) - XML Schemas
  •  Programming Microsoft SQL Server 2005 : The XML Data Type (part 1) - Working with the XML Data Type as a Variable & Working with XML in Tables
  •  SQL Server 2008 : Auditing SQL Server - Creating Server Audit Specifications
  •  SQL Server 2008 : Auditing SQL Server - Creating SQL Server Audits with T-SQL
  •  Programming Microsoft SQL Serve 2005 : An Overview of SQL CLR - Security
  •  Programming Microsoft SQL Serve 2005 : An Overview of SQL CLR - CLR Aggregates
  •  SQL Server 2008: Monitoring Resource Governor
  •  SQL Server 2008: Managing Resources with the Resource Governor (part 3) - Classifier Function
  •  
    PS4 game trailer XBox One game trailer
    WiiU game trailer 3ds game trailer
    Top 10 Video Game
    -   Skyforge [PC] Open Beta Gameplay Trailer
    -   Armored Warfare [PC] PvE Trailer
    -   F1 2015 [PS4/XOne/PC] Features Trailer
    -   Act of Aggression [PC] Pre-Order Trailer
    -   Sword Coast Legends [PC] Campaign Creation E3 2015 Trailer
    -   Sword Coast Legends [PC] Campaign Creation E3 2015 Dungeon Run Trailer
    -   Naruto Shippuden: Ultimate Ninja Storm 4 Trailer
    -   Danganronpa Another Episode: Ultra Despair Girls Trailer 2
    -   Project X Zone 2 Trailer
    -   Poly Bridge Early Access Trailer
    -   Rodea The Sky Soldier Trailer
    -   CABAL 2 Launch Trailer
    -   The Smurfs Trailer
    -   Act of Aggression Pre-Order Trailer
    -   Project X Zone 2 [3DS] Trailer
    Game of War | Kate Upton Commercial
    programming4us
     
     
    programming4us