Implementing New Manageability Features and Practices - Microsoft SQL Server 2008 R2 Administration - page 269

How it works...
The native capability to audit the SQL Server instance level and database-scoped activity is
captured to a target data destination. The Server Audit object defines a target destination of
collected audit events, which is the first step in setting up an audit. The ON_FAILURE option
is important, which indicates the process of writing to the target should continue or stop if
the target is unable to complete the action due to a permission issue. Using a Server Audit
Specification object, the SQL Server instance-scoped events will be captured and forwarded
to a specific server audit object target such as to the file path d:\Entz\FS\ as per the code
or written to the Windows Application event log. We can use the system catalog function fn_
get_audit_file , which returns the information from an audit file created by a server audit.
A Server Audit object creation allows the audit process to designate whether or not the SQL
Server instance should be shut down, if it is unable to write to the target. Once a Server Audit
object is created, we can bind a Server Audit Specification or Database Audit Specification
object to it.
A Server Audit Specification is used to define which events to capture at the SQL Server
instance scope. A Database Audit Specification object allows us to define which events we
wish to capture at the database scope. Only one Server Audit Specification can be bound
to a Server Audit object, whereas one or more Database Audit Specifications can be bound
to a Server Audit object. A single Server Audit object can be collocated with a Server Audit
Specification and one or more Database Audit Specifications. Once the server or database
level audit is enabled, then all the corresponding actions such as any update on a table-
for instance, DML statements-is captured to the audit destination. In our recipe, the audit
action is logged to the Windows event viewer application log and to a log file, for instance:
DBIASSQA_Marketing_Server_Audit_xxxx.SQLAUDIT filename.
Managing server-level securable and
database-level permissions
The security for an SQL Server instance and database is mainly about managing permissions.
These are tied to principals and are securable at a granular level that provides a greater
flexibility and control.
To manage the server-level securable and database-level permissions, it is essential to
know how the SQL Server control access is managed. The top level of the hierarchy is server
scope, which contains logins, the database, and endpoints. The next level is managed on the
database scope, which is contained within the server scope that controls securables such as
database users, schemas, and roles. The bottom level for permissions is the schema scope
that controls the schema itself and objects within the schema such as tables, views, and
stored procedures. The permissions are applied to SQL Server objects on three securable
scopes: server, database, and schema. On the database level, it will enable a principal to
perform actions on securables.