Database Level Auditing in SQL Server 2008 R2

In this video, you will learn how to setup database level auditing in SQL Server 2008 R2. For more information please visit www.thomasliddle.com.

---

Closed Caption:

hello and welcome my name is Thomas
little sequel server DBA and I've been
to deviate now for about 10 years and
today we're going to talk about database
level auditing when you're adding a
database when you're running an instance
of sequel server or an individual
database it tracks and logs events that
occur in the database or the database
engine and sequel server audits that you
create server audits or database audits
and those audits can be ready to hear
the event log or actual audio files for
this demonstration we're going to jump
into doing database level audits and you
can see here in management studio
I've already connected to my sequel
server 2008 instance and we're going to
audit the actual adventureworks database
but before we do that we need to set up
our actual audits we're going to expand
the security folder see here audits
going to right click on that and hit new
audit
you have a number of different options
here in which you can configure first is
the audit name and we're going to call
our audit name adventureworks 2008 r2
and we're going to dl and access on
ok we're actually going to audit dl
changes and access to objects within the
adventureworks database for this
demonstration so after we've done our
audit name
we're going to choose our audit
destination you have three options to
choose from have file security log and
the audit ball now in order to write to
the security log it requires that the
sequel server account be added to the
generate security audits policy within
windows and by default the local system
local service and the network service
are part of this policy but if you're
running your database instance under a
domain account or a windows account
other than the three services i
mentioned you need to ensure that it's
part of the generate security audits
policy in order to write to the security
log for this demonstration we're going
to choose we're actually going to do the
file click on file
and we are going to choose the audit
directory that i actually already set up
this instance
here's that directory
and if you're familiar with sequel
server profile or you have a number of
different options in which you can set
for the actual file you can do max
rollover files which by default is
unlimited
you can also set the maximum file size
ring set the maximum file size 2 10 for
our particular audit now I think I
already set up some audit files so what
I'm going to do is just ensure that we
have a very clean audit directory
we already have one set up so it's good
so now we've created our audit our next
thing to do is to create our
specifications for a particular database
we're gonna do that by expanding the
database going into security and we're
going to database audit specifications
we're going to right-click on this into
a new specification here again we have a
number of options to choose from so
we're going to name our audit
specification called ddl access on its
Peck
we're going to choose the audit that we
had set up previously this is going to
write all the dl and access statements
to this audit we have a number of
different action types that we can
choose from for purposes of our
demonstration again we're going to be
doing dl and access to any object so the
first thing we're gonna do is schema
object change and what this action type
allows you to do is record or log any
event a change to the to any object in
any schema next thing we're going to do
is set up for logging anyone who
accesses any object in any schema we do
this with this particular acts Action
Group schema object action or asset
access group sorry we're going to set
these two particular action types and
then we're going to click OK
after that you'll see that it's expanded
here
the next thing we do is go ahead and
enable this particular on it so we go
back down here right click on it and
click on enable and then we go back into
our database to our database audit
specifications we're going to right
click on that and we're going to click
so now our auditing is all set up and
right now the engine is looking for
changes and access to objects now what
we're going to do is I have this little
query setup that will demonstrate how we
access objects so you'll see here we're
creating a table
we're then going to insert into that
table we're going to select from that
table that's our access to the table and
then we're going to drop that table
we're going to wait three seconds and
then we're going to run this function FN
get audit file and what this function
allows you to do is provide a result set
of the actual audit file and you can
wrap this in a stored procedure or to
your choosing anything you want to do so
we're going to go ahead and run this
see all these particular events a lot of
these events have to do with
intellisense us opening this file we're
going to do is go all the way to the
bottom here right here
you'll see here these particular
statements see that create insert select
and delete or performed on tables
what you do is you can stroll over here
and you can see in our statement column
to create table the insert to select and
the drop
so you can actually see the access to
these tables you can see the object the
schema the database name you can
actually see who accessed it so in our
case of assist EBA since i'm logged in
fiber to login with another account
anybody accessing that object the server
principal would be there
ok so that's database level auditing my
name's Tom little if you have any
questions please give me and please drop
me an email
it's t little that's t li DD l e the
number 30 @ gmail.com or you can visit
me on my website that's www.target.com
thank you