Database Simplified Headline Animator

Database Simplified

Saturday 29 October 2011

Policy In SQL Server

SQL Server 2008 +

Policy based management introduced in sql server 2008 is really a nice feature. Where DBA can manage certain activity by enforcing policy based on specified conditions. You can enforce policy at database level.

How To Create Policy ?

The following step by step shows creation of policy for a stored procedure name standard.

Create a condition for policy

image

Create A Condition Expression

image

Facet : Stored Procedure, Field = @Name and Value=’Usp_%’, It Means that any stored procedure name must start with “Usp_” If it does not starts with Usp_ then user won’t be able to create that stored procedure.

Now Condition has been created, Next step is to create policy on condition create above.

How To Create Policy ?

image

Define Policy

image

Here "Pol_Create_Stored_Procedure”  has been defined with condition we created above which will be targeting all the databases and evaluation mode has been set to On Change prevent. these evaluation mode depends on facet we define in condition. Following is the list of evaluation mode.

On demand - The policy is evaluated only when directly ran by the administrator.
On change: prevent - DDL triggers are used to prevent policy violations.
On change: log only - Event notifications are used to check a policy when a change is made.
On schedule - A SQL Agent job is used to periodically check policies for violations.

Now Lets Try to Create a Stored Procedure

image

You can see the above screen shot where I tried to create stored procedure “Sp_GetData” which doesn’t pass the policy and resulted in batch aborted.

Now Let Try to use Usp_

image 

So you saw that you can enforce policy to following standard naming conventions. There are many facets on which condition and policy can be defined.

Friday 28 October 2011

Create SNAPSHOT In SQL Server

SQL SERVER 2005 + (Only Enterprise Edition) 

SNAPSHOTS are read-only views of actual database of a  particular time and are really useful for sharing load for reporting purpose. You can have multiple snapshot against a single database.

Working of SNAPHOT ?

Suppose you have created SNAPHOT of database at 9:00 AM. You will see that (.ss) snapshot file size is very small initially and as you modify actual database data, the size of this (.ss)  file increases. This is because whenever user modifies actual database record the older records (only First time since snapshot was created) are moved to snapshot database file (.ss). This is done to maintain SNAPSHOT State. In our case it is 9:00 AM

So one must keep this in mind that if you are planning to have multiple snapshot then it may be an overhead and come up as performance bottleneck.

How To Create SNAPHOT ?

Syntax :



Create database <SnapShotName> On
(Name =<ActualDatabase Data FileName >, FILENAME='.ss File Path')
As SNAPSHOT OF <ACtual Data Name>

How To Check Actual Database Data File Name ?

image

Example : To Create SNAPSHOT



Create database CDB_SNP On
(Name =CDB, FILENAME='D:\CDB_NSP_Data.ss')
As SNAPSHOT OF CDB
Go

As You Can Create Multiple SNAPSHOT



Create database CDB_SNP2 On
(Name =CDB, FILENAME='D:\CDB_NSP_Data2.ss')
As SNAPSHOT OF CDB
Go

 

See Your SNAPSHOT In SSMS Object Explorer

image

Password Protected Backup In SQL Server

Password protected database backup : Setting backup media password are important and useful for securing misuse of data. If database backup is password protected then one will not be able to restore and see details from backup file using RESTOREFILELIST or HEADERONLY command.

How to take password protected database backup?

Create A Database



If DB_ID('DBS_Test') Is null
Create Database DBS_Test
Go

Backup Database Backup With Password



Backup Database DBS_Test To Disk ='D:\DBS_Test.BAK' With MediaPassword='password'
Go

Drop The Existing Database



Drop Database DBS_Test
Go

Try To Get FileList Info From Backup



Restore FILELISTONLY  From Disk='D:\DBS_Test.BAK'

Output :

Msg 3279, Level 16, State 6, Line 1
Access is denied due to a password failure
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

Throws error since no password has been supplied with query.

Try To Get DB Info From Backup



Restore HEADERONLY  From Disk='D:\DBS_Test.BAK'

Output:

Msg 3279, Level 16, State 6, Line 1
Access is denied due to a password failure
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.

Same as above throws error since no password has been supplied with query.

Now Try To Restore Database With Backup File



Restore Database DBS_Test From Disk='D:\DBS_Test.BAK'

Output:

Msg 3279, Level 16, State 6, Line 1
Access is denied due to a password failure
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

So, you have seen that no details or info can be taken from password protected database backup.

How to Get Info and Restore Password Protected Database Backup ?

Get File List Info From Password Protected Database Backup



Restore FILELISTONLY  From Disk='D:\DBS_Test.BAK'
With MEDIAPASSWORD='password'
Output: See Here

Get Database Info From Password Protected Database Backup



Restore HEADERONLY  From Disk='D:\DBS_Test.BAK'
With MEDIAPASSWORD='password'

Output : See Here

Restore Database From Password Protected Database Backup



Restore Database DBS_Test From Disk='D:\DBS_Test.BAK'
With MEDIAPASSWORD='password'

Output :

Processed 168 pages for database 'DBS_Test', file 'DBS_Test' on file 1.
Processed 2 pages for database 'DBS_Test', file 'DBS_Test_log' on file 1.
RESTORE DATABASE successfully processed 170 pages in 0.214 seconds (6.197 MB/sec).

Get Information From Backup File In SQL Server

This post describes how can we get information from backup file without restoring it. information about file groups, data and log files and Database information like server name and user name, internal database version, backup size and etc.

Backup A Database



Backup Database CDB To Disk='D:\CDB_Full.Bak'  With MediaName='CDB_FullBackup'
Go

Now we have a backup file located at D:\CDB_full.Bak, With this file following query retrieves information about database Name,UserName, ServerName and Backup Date and etc.



Restore HEADERONLY  From Disk='D:\CDB_Full.BAK'
Go

image

Now let’s get filegroup and file info from backup



Restore FILELISTONLY From Disk='D:\CDB_Full.BAK'
Go

image

Its really useful when you want to have information about a backup file without restoring it.

Wednesday 26 October 2011

Configuring Database Mail In SQL Server Step By Step

To Configure Database Mail you must create database mail account and profile.Following are the steps to configure Database Mail Account And Profile.

image

image

image

image

image

image

image

image

image

image

Once Account and Profile has been created you must configure database mail using sp_configure stored procedure

exec sp_configure 'show advanced options',1
Go

Reconfigure
go

sp_configure 'Database Mail XPs',1
go

Reconfigure
go

Now the database mail configuration is done. Lets send a test mail.

 

image

image

Or you can use following query to send database mail.



EXEC msdb.dbo.sp_send_dbmail @profile_name=’Demo Profile’,
@recipients='kuldeep@dbsimplified.com',
@subject='Demo Mail Sent Using T-SQL Query',
@body='This is test email sent from database mail using T-SQL Query’
 

image

image

Create User In All The SQL Servers

I have found many people questioning on MSDN forum “How to run a query or create a user in all sql server instances” and the answer is using Central Management Server (Registered Servers). see following example of creating a user in all the sql server instances.

I have following three instances of SQL where we need to create a user using registered servers.

image

 

Goto Menu : View—>Registered Servers

image

You will have registered servers pane on your screen. See following.

image 

You see a “Local Server Groups” folder under Database Engine. You can add all your instances under this group see following how to add ?

Right Click On Local Server Group and Click New Server Registration

image

You will get following connection dialogue. Using the following dialogue you can register the sql server instance under the local server group. You can register multiple instances of SQL Server or The number of instances on which you want to run a query.

image

Now after registering the sql server instance you need to open a query window for these registered servers see following.

Now right click on Local Server Group and choose New Query.

image

 

Now Create Database in all the instances using following query.



Create Database CommonDb

Output :

Command(s) completed successfully. (3 servers)

It means Query is successfully executed on 3 server.

Now Create a User in all the instances



CREATE LOGIN [CommonUser] WITH PASSWORD=N'Admin@123'

Output :

Command(s) completed successfully. (3 servers)

 

So, Central Server management is really useful and nice feature when you are working with multiple and large number of sql instances.

Wednesday 19 October 2011

ISNULL VS COALESCE, Nested ISNULL

There is a myth bound to COALESCE function that it is just a replacement of ISNULL. Many candidate in interview also agree to this statement that COALESCE is just replacement of ISNULL but most of them were not aware of advantage of using COALESCE in place of ISNULL.

So Lets See ISNULL VS COALESCE



Test 1 : Similar Functionality Of ISNULL and COALESCE

Use of ISNULL



Declare @Id1 Int
Declare @Id2 Int
Set @Id1=10
Select ISNULL(@Id2,@Id1) as Value

Returns 10 as Expected

Use of COLESCE



Declare @Id1 Int
Declare @Id2 Int
Set @Id1=10
Select COALESCE(@Id2,@Id1) as Value

Returns 10 as well.

So Its Proved That Both Provided Same Functionality


Test 2 : Mixing DataType

Use ISNULL with Mixed DataType



Declare @Var1 Int
Declare @Var2 DateTime
Set @Var2=GETDATE()
Select ISNULL(@Var1,@Var2) as Value

Output : Throws Error

Msg 257, Level 16, State 3, Line 4
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

Use COALESCE with Mixed DataType



Declare @Var1 Int
Declare @Var2 DateTime
Set @Var2=GETDATE()
Select ISNULL(@Var1,@Var2) as Value

Output : Return CurrentDate

So its clear that ISNULL only support checking of similar DataType whereas COALESCE can be used with different data type as you saw in example above.


Test 3 : Nested ISNULL VS NESTED COALESCE

To perform TEST 3 create table and populate data with following script.



Create Table NextedNull
(
Id Int Identity(1,1),
Slot1 Varchar(10),
Slot2 Varchar(10),
Slot3 Varchar(10),
Slot4 Varchar(10),
Slot5 Varchar(10)
)
Go

Insert Into NextedNull Values
(null,null,null,null,'Active'),
(null,'Active',null,null,null),
('Active',null,null,null,null),
(null,null,'Active',null,null),
(null,null,null,null,null)
Go

Select * From NextedNull
Go

Output :

image


Now the task is to check Active slot against each Id in this table and if any slot is active then result should be Active else it should return ‘No Active Slot’.

Using ISNULL



Select Id,ISNULL(Slot1,Isnull(Slot2,ISnull(Slot3,Isnull(Slot4,Isnull(Slot5,'No Active Slot'))))) IsActive
from NextedNull
Go

Ouptut :

image

lets try to achieve above task with COALESCE.



Select Id,COALESCE(Slot1,Slot2,Slot3,Slot4,Slot5,'No Active Slot') IsActive
From NextedNull
Go

Output :

image

So we have seen the flexibility and benefits of using COALESCE over ISNULL and we found that COALESCE provides more functionality then just replacing role of ISNULL.

Monday 17 October 2011

Query To Find SQL Server Service Account

SQL Server Services Account are stored in registry keys. You can read those key using built-in stored procedure “xp_instance_regread”.  These account information are stored under following keys HKEY_LOCAL_MACHINE --> SYSTEM --> CurrentControlSet --> services –> InstanceName

Instance Name: It is MSSQLServer for Default Instance. and if you have named instance then it is MSSQL$instancename. For example if you instance name is “Server\Prod” so your instance will be MSSQL$Prod.

Now you can use following query to find service account Name of SQL Server Service.



DECLARE @ServiceaccountName varchar(250)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',@ServiceAccountName OUTPUT, N'no_output'
SELECT @ServiceAccountName

Similarly you can also get Account Name For SQL Server Agent. Following query gets you SQL Server Agent Account for default instance. You can specify SQLAgent$Instancename if you have named instance installed.



DECLARE @ServiceaccountName varchar(250)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\SQLServeragent',
N'ObjectName',@ServiceAccountName OUTPUT, N'no_output'
SELECT @ServiceAccountName

Change Windows Authentication To Mixed Mode In SQL Server

This post describes changing Window Authentication mode to Mixed Authentication mode in SQL Server. When you install SQL Server by default window authentication is installed If you do not choose mixed authentication at the time of SQL Server installation. However these modes can be changed even after installation of SQL Server. Let’s See How We Can Change Window Authentication of Mixed Mode.

Note : Changing Mode From Window To Mixed Or Visa Versa Requires SQL Server Service Restart.

Step 1 : Start SQL Server Management Studio

In Object Explorer Right Click In ServerName and Select Properties As Can be seen in following image.

image

Step 2 : You will Get Server Property Dialogue Box.

In Property Dialogue Box Select Security Option and under Server authentication Select “SQL Server and Window Authentication mode” and Click OK.

image

Step 3  : Enable “sa” login (Optional)

sa login is disabled by default when SQL Server is installed with window authentication. So when you choose mixed authentication you can also enable sa login (which is disabled by default).

Goto  ServerName in Object Explorer –> Security –> Logins –> Choose sa login name. Right Click on login name (sa) and click properties option see following screen shot.

image

you get following dialogue box  (login property), Under General Page. You need to set strong password and can choose option like “Enforce Password policy” making you password more secure.

image

Goto Status Page : See Following Screen Shot and Select Enable. Click Ok.  Now Re-Start SQL Server Service and login with SQL Server Authentication.

image

Thursday 13 October 2011

Server Side Trace In SQL Server

Running SQL Server Profiler trace are considered to be very costly operation. We generally minimize the load of Profiler trace by filtering the columns and limiting the event in trace. Alternatively we have “Server Side Trace” The Server site trace provides most effective way to trace then SQL Profiler. Server side trace runs on Server so no network congestion takes place. Server side trace runs in background so user need not to worry about keeping trace running on machine all the time as we do with SQL Profiler.

How To Run Server Side Trace

  • Start SQL Server Profiler
    • Choose All the Event and Information Required To Trace.
    • Click Run

image

  • Goto File Menu –> Export –>Script Trace Definition and Choose Appropriate Version Script Option.
    • Save To Script To File
    • Stop SQL Profiler
    • Close The SQL Profiler

image

  • Open The File Where Script Was Saved Copy The Script and Paste To Query Window. See Following Script For Example
    • You Must Write You FileName with Path In Place Of “InsertFileNameHere” In Below Script.
    • You can also set value for variable “@maxfilesize” which sets maximum trace file sixe in MB.
    • Now Execute the The Script

 



/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler          */
/* Date: 10/14/2011  00:06:22 AM         */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network shareexec @rc = sp_trace_create @TraceID output, 2, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 1f53f9c2-0fe2-4eac-9371-4eb6c496d329'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go

In the Above Query You can see the line


exec @rc = sp_trace_create @TraceID output, 2, N'InsertFileNameHere', @maxfilesize, NULL

Where 2 means file will be rollover once file has been filled up. mean you will have multiple sets of @maxfilesize defined. If you don’t mention 2 then trace will be stopped when file size reaches @maxfilesize Defined.
So once trace is started you can monitor trace info with following query. which gives you information about trace.



select * from  fn_trace_getinfo(default)
--OR
select * from sys.traces

Output :

image

As you can see above screen show show traceid and trace filename. you can view the trace using following function.

image

The trace is keeps on running in the background until it is disabled or stopped by user or server is stopped.

How to Disable or Stop Trace ?



Exec sp_trace_setstatus @traceId=1,@Enabled=0 --To Disable Trace
Go
Exec sp_trace_setstatus @traceId=1,@Enabled=1 --To Enable Trace
Go
Exec sp_trace_setstatus @traceId=1,@Enabled=2 –To Stop Trace

You Must Disable Trace Inorder To Stop A Trace. Once You Have Stopped the Trace Following Query Will Not Return Any Row For Stopped Trace.



Select * from sys.traces

Wednesday 12 October 2011

Unable to modify table. Timeout expired In SQL Server

This notification is generally encountered when you are trying to modify a large table probably added a column in between or changing the datatype of one or more fields which are generally very costly operation and time consuming. You can avoid this message by applying any of methods below.

  • Start SSMS –> Goto Tools Menu –>Option –> Under Option Dialogue Choose Designers—>Table and Database Designers
    • Uncheck  “Override connection string time-out value for table designer update

image

  • Start SSMS –> Goto Tools Menu –>Option –> Under Option Dialogue Choose Designers—>Table and Database Designers
    • Specify the high value for “Transaction time-out after:

image

 

  • You Can Create Script Of Table Changes and Run It as Query. See Following Screen Shot To Generate Query For Table Changes

image

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.

This message is faced when you try to make change in table design using SSMS (SQL Server Management Studio). This is default setting of SQL Server SSMS that it doesn’t let user to modify the schema of table and throws the message on saving. The setting is “Prevent saving changes that require table re-creation”. It can be changed through SSMS (SQL Server Management Studio).

  • Start SSMS(SQL Server Management Studio)
  • Goto Tools Menu and Choose Options (Option Dialog Will Be Opened)
  • In Option Dialogue Click Designers ---> Table and Database Designers
  • Uncheck the Option : Prevent saving changes that required table  re-creation
  • Click Ok

See Following Screen Shot

image

Sunday 9 October 2011

View User Wise Permissions In SQL Server

 

Query To Find Server Roles User Wise



SELECT a.name as LoginName,a.type_desc AS LoginType, a.default_database_name AS DefaultDBName,
CASE WHEN b.sysadmin = 1 THEN 'sysadmin'
          WHEN b.securityadmin=1 THEN 'securityadmin'
          WHEN b.serveradmin=1 THEN 'serveradmin'
          WHEN b.setupadmin=1 THEN 'setupadmin'
          WHEN b.processadmin=1 THEN 'processadmin'
          WHEN b.diskadmin=1 THEN 'diskadmin'
          WHEN b.dbcreator=1 THEN 'dbcreator'
          WHEN b.bulkadmin=1 THEN 'bulkadmin'
          ELSE 'Public' END AS 'ServerRole'
FROM sys.server_principals a  JOIN master..syslogins b ON a.sid=b.sid WHERE a.type  <> 'R'
and a.name not Like '##%'

Query To Find Database Wise User Permissions


DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @DBuser_table TABLE
(
DBName VARCHAR(200),
UserName VARCHAR(250),
LoginType VARCHAR(500),
AssociatedRole VARCHAR(200)
)
SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,
a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole
FROM ?.sys.database_principals a
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL
AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1
AND a.name NOT LIKE ''##%''
AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'')
ORDER BY Name'
INSERT @DBuser_table
EXEC sp_MSforeachdb @command1=@dbuser_sql
SELECT * FROM @DBuser_table ORDER BY DBName

Query To Find Object Wise User Permissions



DECLARE @Obj_sql VARCHAR(2000)
DECLARE @Obj_table TABLE (
DBName VARCHAR(200),
UserName VARCHAR(250),
ObjectName VARCHAR(500),
Permission VARCHAR(200))
SET @Obj_sql='select ''?'' as DBName,U.name as username, O.name as object,  permission_name as permission
from ?.sys.database_permissions
join ?.sys.sysusers U on grantee_principal_id = uid
join ?.sys.sysobjects O on major_id = id
WHERE ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'')
order by U.name '
INSERT @Obj_table
EXEC sp_msforeachdb @command1=@Obj_sql
SELECT * FROM @Obj_table

Saturday 8 October 2011

Auditing In SQL Server

SQL Server 2008 Enterprise +

SQL Auditing: Auditing is most demanding activity as we need to audit many things time to time and on demand in any system. Starting with SQL Server 2008 Enterprise we have excellent audit feature that can be used to audit Server (SQL Instance)  ( Server Audit Specification) or Database Level Activity (Database Audit Specification). There are many action group on which auditing can be configured. We will demonstrate Creating Database audit (Instance Level) and Delete And Update DML(On Database Level).

Audit Can be Configured Using SSMS (SQL Server Management Studio) Or Using T-SQL Queries.

In Order To Create Server Audit Specification or Database Audit Specification We Need To Create SQL Server Audit Objects.

The SQL Server Audit object captures SQL instance or database-level actions and groups of actions. The audit is at the SQL Server instance level. You can have multiple audits per SQL Server instance.

Create A Server Audit

Select Server Instance –> Security –> Audit—>Right Click and Select New Audit

image

When you define an audit, you specify the  audit destination. Audit destination can be (File, Security Log, Application Log) , For This Demonstration I am using File.  You can specify Audit Name, Audit Destination, Audit File Path, Max Size of Audit File. So Once you click Ok Your Server Audit Created But by default it is disabled so you need to enabled it by right clicking on audit created and choose Enable Audit.

Now your server audit is ready you need to configure Server Audit Specification to Capture Create or Drop Databases.

Select SQL Instance –> Security –> Server Audit Specification –> Right Click and Choose New Server Audit Specification

image

You Will Get The Following Dialogue Box as Above, You Can Select The Server Audit and In Audit Action Type You Can Select Multiple Audit Action I have selected Database_Change_Group Since we need to Audit Create And Drop Database in SQL Server Instance.

 

Now Click Ok. and Same As Server Audit These Server Audit Specifications Are Disabled By Default So You Need to Enable Them.


Now Set Database Audit Specification For Insert, Delete And Update. When you want to define Database Audit Specification then you need to set it for individual database.

To Check Database Audit Specification Create A Database With Following Script



Create Database ON
Go

Use ONP
Go
Create Table OrderMaster
(
Id Int Identity(1,1),
OrderNo Varchar(20),
CompanyName Varchar(50),
OrderDate DateTime
)
Go

Select SQL Instance —> Databases –-> Select Database on which Audit Has To Be configured --->

image

For DML Audit I have Selected DELETE, UPDATE, INSERT Audit Action on Database Audit Specification. Under Object Class I have Selected Database Which means DML on any table in the database. You can select a Particular object or Particular Schema For Audit. Next is Object Name Which is related to Object Class Since Class is Database so Object name will be a database Name. So If any DML takes place in any table under ONP Database those action will be audited. The Last One Is Principal using principal you can set the audit for a particular user or a particular role. I have given public as every user is tagger to this role. so in this case action from every user will be audited.

 

Now Click Ok and And Enable the Database Audit Specification same as we did for Server Audit and Server Audit Specification.

Now open A Query Window And Run Following Commands

--To Generate Event for Database Chagen Group



Use Master
Create Database MyTest
Go
Drop Database MyTest
Go

--For DML Operation On ONP Database



Use ONP
Go
Insert Into OrderMaster Values
('EO340','ASOS.Com',GETDATE()),('EO341','Next.Inc',GETDATE())
Update OrderMaster Set CompanyName='ASPL' Where Id=1
Delete From OrderMaster Where Id=1
Go

Now You Check You Audit

SQL Instance –> Security –> Audit –>Right Click On Audit  (“My_Local_Instance_Audit” we created it for demonstration) –> and Choose View Audit Logs.

You will Get Following Screen

image

You can see that every audit operation has been logged aand show command executed, user name and date and etc. You Also View Audit Log With System Function



Select * from Sys.fn_get_audit_file('FleName',default,default)

How to Know File Name ? You can get the file name by checking the path given in Server Audit the extension of Audit File Is  .sqlaudit



Select event_time,action_id,server_principal_name,statement,*
from Sys.fn_get_audit_file(
'D:\DBBack\Audit_My_Local_Instance_F7C6701F-42C0-4096-8E7B-676785E65645_0_129625425056510000.SQLAUDIT',
default,default)

If You Have Multiple Audit Files then You Can Use Following



Select event_time,action_id,server_principal_name,statement,*
from Sys.fn_get_audit_file('D:\DBBack\*.sqlaudit',default,default)

Output :

image

So auditing now is very easy and flexible. There is long list of Actions which can be audited.