Forthcoming Articles

Multi-server administration of sql server agent
master and target

SQL Server Agent Jobs

Q) What are the sub-nodes under sql server agent ?
A)
Jobs
Job Activity Monitor
Alerts
Operators
Proxies
Error Logs
Here error logs is the error log for sql server agent and not for the sql server instance.You find the error log for server instance under management node.

Q) How to create a job ?
A)
use the following sp :
exec msdb.dbo.sp_add_job

Example :
exec msdb.dbo.sp_add_job
@job_name='backupdbkali',
@enabled=1,
@description='this takes backup of db kali',
@category_id=3,
@owner_login_name='home-bix5t88dr6\administrator',
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@notify_email_operator_name='sudarsan',
@delete_level=0.

This example job creates a job that is going to perform a backup of db kali.

Q) How to supply category_id value as input in sp_add_job stored procedure ? or How to find the existing category id's and their description ?
A)
exec msdb.dbo.sp_help_category

Q) What are the possible notify level values that can be supplied as inputs in stored procedure sp_add_job ?
A)
0-Never
1-Success
2-Failure
3-Always

Q) Job can contain multiple steps.Now how to add a step to a job ?
A) Use the following stored procedure :
exec msdb.dbo.sp_add_jobstep

Example :
exec msdb.dbo.sp_add_jobstep
@job_name='backupdbkali',
@step_id=1,
@step_name='backsup kalidb',
@subsystem='TSQL',
@command='use master go backup database kali to disk=''C:\backups\kali.bak''',
@on_success_action=1,
@on_fail_action=2,
@server='home-bix5t88dr6\csfspd1caspro',
@database_name='master',
@database_user_name='dbo',
@retry_attempts=3,
@retry_interval=10,
@output_file_name='C:\backups\outputfilename.txt',
@flags=2

Q) What are the subsystems values in sp_add_jobstep ?
A)
Value - Description

'ACTIVESCRIPTING' - Active Script
'CMDEXEC'-Operating-system command or executable program
'DISTRIBUTION'-Replication Distribution Agent job
'SNAPSHOT'- Replication Snapshot Agent job
'LOGREADER' -Replication Log Reader Agent job
'MERGE'- Replication Merge Agent job
'QueueReader'-Replication Queue Reader Agent job
'ANALYSISQUERY' -Analysis Services query (MDX, DMX).
'ANALYSISCOMMAND'- Analysis Services command (XMLA).
'Dts' -Integration Services package execution
'PowerShell'- PowerShell Script
'TSQL' -(default) Transact-SQL statement

Q) What are the possible on success and on failure option values in sp_add_jobstep ?
A)
Value - Description (action)

1 (default) -Quit with success
2 -Quit with failure
3- Go to next step
4 -Go to step on_success_step_id.

Q) What are possible flag values in sp_add_jobstep ?
A)
Value - Description

0 (default)- Overwrite output file
2 -Append to output file
4 -Write Transact-SQL job step output to step history
8 -Write log to table (overwrite existing history)
16 -Write log to table (append to existing history)


Q) How to schedule a job to run at our customized intervals ?
A)
exec msdb.dbo.sp_add_jobschedule

Q) How to create an alert ?
A)
exec msdb.dbo.sp_add_alert

Q) How to setup notification for an alert ?
A)
exec msdb.dbo.sp_add_notification

Q) How to create an operator (notification recipient) for use with alerts and jobs ?
A)
exec msdb.dbo.sp_add_operator

Q) How to Create a schedule that can be used by any number of jobs ?
A)
exec msdb.dbo.sp_add_schedule

Q) How to attach a schedule to a job ?
A)
exec msdb.dbo.sp_attach_schedule

Q) How to Close the current SQL Server Agent error log file and cycle the SQL Server Agent error log extension numbers just like a server agent restart ? (reinstantiating sql server agent error log)
A)
exec msdb.dbo.sp_cycle_agent_errorlog

Q) How to close the current server error log file and cycle the error log extension numbers just like a server restart ?
(reinstantiating sql server error log)
A)
exec msdb.dbo.sp_cycle_errorlog

Q) How to find information about jobs that are used by SQL Server Agent to perform automated activities in SQL Server ?
A)
exec msdb.dbo.sp_help_job

Q) How to know information about the runtime state of SQL Server Agent jobs ?
A)
exec msdb.dbo.sp_help_jobactivity

Q) How to know the number of jobs i.e. count that a particular schedule is attached to ?
A)
exec msdb.dbo.sp_help_jobcount @schedule_id=9

Q) How to know the information about all those jobs that a particular schedule is attached to ?
A)
exec msdb.dbo.sp_help_jobs_in_schedule @schedule_id=9

Q) How to know information for the steps in a job used by SQL Server Agent service to perform automated activities ?
A)
exec msdb.dbo.sp_help_jobstep

Q) How to know a list of alerts for a given operator or a list of operators for a given alert ?
A)
exec msdb.dbo.sp_help_notification
Example :
EXEC msdb.dbo.sp_help_notification
@object_type = N'ALERTS',
@name = N'sudarsan',
@enum_type = N'ACTUAL',
@notification_method = 7

Q) How to find information about the operators defined for the server ?
A)
exec msdb.dbo.sp_help_operator

Q) How to know all the created proxies by dbo or anyone else with necessary permissions of creating a proxy ?
A)
exec msdb.dbo.sp_help_proxy

Q) How to know all the existing schedules ?
A)
exec msdb.dbo.sp_help_schedule

Q) How to Delete or reassign jobs that belong to the specified login ?
A)
sp_manage_jobs_by_login
[ @action = ] 'action'
[, [@current_owner_login_name = ] 'current_owner_login_name']
[, [@new_owner_login_name = ] 'new_owner_login_name']

Example :
USE msdb ;
GO

EXEC dbo.sp_manage_jobs_by_login
@action = N'REASSIGN',
@current_owner_login_name = N'danw',
@new_owner_login_name = N'françoisa' ;
GO

or

USE msdb ;
GO

EXEC dbo.sp_manage_jobs_by_login
@action = N'DELETE',
@current_owner_login_name = N'danw'

GO


Q) How to add a operator ?
A)
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'blackclouds',
@enabled=1,
@pager_days=0,
@email_address=N'black.clouds.999@gmail.com'
GO

Q) How to notify an operator ?
A)
exec msdb.dbo. sp_notify_operator
[ @profile_name = ] 'profilename' ,
[ @id = ] id ,
[ @name = ] 'blackclouds' ,
[ @subject = ] 'subject' ,
[ @body = ] 'message' ,
[ @file_attachments = ] 'attachment'
[ @mail_database = ] 'mail_host_database'

Q) How to revokes access for the login "terrid" to access the proxy "Catalog application proxy" ?
A)
USE msdb ;
GO


EXEC dbo.sp_revoke_login_from_proxy
@name = N'terrid',
@proxy_name = N'Catalog application proxy' ;
GO

Q) How to Revoke access to a subsystem from a proxy ?
A)
USE msdb ;
GO

EXEC dbo.sp_revoke_proxy_from_subsystem
@proxy_name = 'Catalog application proxy',
@subsystem_name = N'Dts';

Q) How to know information about the alerts defined for the server ?
A)
exec msdb.dbo.sp_help_alert

Q) How to instruct SQL Server Agent to execute a job immediately ?
A)
exec msdb.dbo.sp_start_job @job_name='backup_system_db'

Q) How to instruct SQL Server Agent to stop the execution of a job ?
A)
exec msdb.dbo.sp_stop_job @job_name='backup_system_db'

Q) How to Grant a security principal access to a proxy ?
A)
USE msdb ;
GO

EXEC dbo.sp_grant_login_to_proxy
@login_name = N'adventure-works\terrid',
@proxy_name = N'Catalog application proxy' ;
GO

Q) How to Grant a proxy access to a subsystem ?
A)
USE msdb ;
GO

EXEC dbo.sp_grant_proxy_to_subsystem
@proxy_name = 'Catalog application proxy',
@subsystem_id = 2;
GO

Q) List all the SQL Server Agent subsystems.give query.
A)
exec msdb.dbo.sp_enum_sqlagent_subsystems

Q) Lists permissions for SQL Server Agent proxies to access subsystems.
A)
exec msdb.dbo.sp_enum_proxy_for_subsystem

Q) Lists associations between security principals and proxies.
A)
exec msdb.dbo.sp_enum_login_for_proxy

Q) How to Remove the history records for a job ?
A)


The following example removes the history for a job named NightlyBackups.

USE msdb ;
GO

EXEC dbo.sp_purge_jobhistory
@job_name = N'NightlyBackups' ;
GO

The following example executes the procedure with no parameters to remove all history records.

USE msdb ;
GO

EXEC dbo.sp_purge_jobhistory ;
GO


Q) How to remove an association between a schedule and a job ?
A)
The following example removes an association between a 'NightlyJobs' schedule and a 'BackupDatabase' job.

USE msdb ;
GO

EXEC dbo.sp_detach_schedule
@job_name = 'BackupDatabase',
@schedule_name = 'NightlyJobs' ;
GO


Q)Write short notes on sp_add_category ?
A)
Adds the specified category of jobs, alerts, or operators to the server.


Syntax

sp_add_category
[ [ @class = ] 'class', ]
[ [ @type = ] 'type', ]
{ [ @name = ] 'name' }

Arguments

[ @class = ] 'class'

The class of the category to be added. class is varchar(8) with a default value of JOB, and can be one of these values.
Value Description

JOB Adds a job category.

ALERT Adds an alert category.

OPERATOR Adds an operator category.

[ @type = ] 'type'

The type of category to be added. type is varchar(12), with a default value of LOCAL, and can be one of these values.
Value Description

LOCAL A local job category.

MULTI-SERVER A multiserver job category.

NONE A category for a class other than JOB.

[ @name = ] 'name'

The name of the category to be added. The name must be unique within the specified class. name is sysname, with no default.

Return Code Values

0 (success) or 1 (failure)


sp_add_category must be run from the msdb database.
Permissions

Only members of the sysadmin fixed server role can execute sp_add_category.
Examples

The following example creates a local job category named AdminJobs.

USE msdb ;
GO

EXEC dbo.sp_add_category
@class=N'JOB',
@type=N'LOCAL',
@name=N'AdminJobs' ;
GO


Q) Write short notes on sp_add_jobserver.
A)
Targets the specified job at the specified server.

Topic link icon Transact-SQL Syntax Conventions
Syntax

sp_add_jobserver [ @job_id = ] job_id | [ @job_name = ] 'job_name'
[ , [ @server_name = ] 'server' ]

Arguments

[ @job_id = ] job_id

The identification number of the job. job_id is uniqueidentifier, with a default of NULL.

[ @job_name = ] 'job_name'

The name of the job. job_name is sysname, with a default of NULL.
ms178625.note(en-us,SQL.100).gifNote:
Either job_id or job_name must be specified, but both cannot be specified.

[ @server_name = ] 'server'

The name of the server at which to target the job. server is nvarchar(30), with a default of N'(LOCAL)'. server can be either (LOCAL) for a local server, or the name of an existing target server.

Return Code Values

0 (success) or 1 (failure)
Result Sets

None
Remarks

@automatic_post exists in sp_add_jobserver, but is not listed under Arguments. @automatic_post is reserved for internal use.

SQL Server Management Studio provides an easy, graphical way to manage jobs, and is the recommended way to create and manage the job infrastructure.
Permissions

By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:

* SQLAgentUserRole
* SQLAgentReaderRole
* SQLAgentOperatorRole

For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.

Only members of the sysadmin fixed server role can execute sp_add_jobserver for jobs that involve multiple servers.
Examples
A. Assigning a job to the local server

The following example assigns the job NightlyBackups to run on the local server.
ms178625.note(en-us,SQL.100).gifNote:
This example assumes that the NightlyBackups job already exists.

USE msdb ;
GO

EXEC dbo.sp_add_jobserver
@job_name = N'NightlyBackups' ;
GO

B. Assigning a job to run on a different server

The following example assigns the multiserver job Weekly Sales Backups to the server SEATTLE2.
ms178625.note(en-us,SQL.100).gifNote:
This example assumes that the Weekly Sales Backups job already exists and that SEATTLE2 is registered as a target server for the current instance.

USE msdb ;
GO

EXEC dbo.sp_add_jobserver
@job_name = N'Weekly Sales Backups',
@server_name = N'SEATTLE2' ;
GO

Q) How to update a job ?
A)
use the sp as follows :
exec msdb.dbo.sp_update_job.


1) If you want to either update or delete regarding jobs alerts etc etc regarding this then you need to simply replace
exec msdb.dbo.sp_add_alert
or
exec msdb.dbo.sp_help_alert

to

exec msdb.dbo.sp_update-alert
or
exec msdb.dbo.sp_delete_alert.

use delete here and not drop.

2) If no proxy_id is specified, no proxy_name is specified, and no user_name is specified, the job step runs as the service account for SQL Server Agent.

SQL Server Agent Jobs Examples !

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'backup_system_db',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'Database Maintenance',
@owner_login_name=N'HOME-BIX5T88DR6\Administrator',
@notify_email_operator_name=N'sudarsan', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'backup_system_db', @server_name = N'HOME-BIX5T88DR6\CSFSPD1CASPRO'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'backup_system_db', @step_name=N'master_backup',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=3,
@retry_interval=2,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'backup database master
to disk=N''G:\Backups\mast.bak''
with init,stats=25,description=''master db backup'';
GO

print ''backup of master db successfully completed on''+convert(varchar(50), GETDATE())',
@database_name=N'master',
@database_user_name=N'dbo',
@output_file_name=N'G:\Backups\outputonbackup',
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'backup_system_db',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'',
@category_name=N'Database Maintenance',
@owner_login_name=N'HOME-BIX5T88DR6\Administrator',
@notify_email_operator_name=N'sudarsan',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'backup_system_db', @name=N'schedule1',
@enabled=1,
@freq_type=8,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20100208,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO

The above job simply creates a backup of master db,outputs the step output to a file.

Example2 :

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'alert2',
@enabled=1,
@delay_between_responses=120,
@include_event_description_in=0,
@performance_condition=N'MSSQL$CSFSPD1CASPRO:Databases|Data File(s) Size (KB)|kali|>|256000',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'alert2', @operator_name=N'sudarsan', @notification_method = 1
GO

Database Security !

Q) How to view all the server principals ?
A)
select * from sys.server_principals

Q) How to view all the server permissions ?
A)
select * from sys.server_permissions

Q) How to view all the sql logins ?
A)
select * from sys.sql_logins

Q) How to find each executable system object that can be enabled or disabled by a surface area configuration component ?
A)
select * from sys.system_components_surface_area_configuration
-------------------------------------------------------------------------
Now lets talk in detail about Database security.

Q) By default how many db users exist and what are they ?
A)
dbo
guest
information_schema
sys

Script is as follows :
USE [kali]
GO
CREATE USER [sys]
GO
CREATE USER [INFORMATION_SCHEMA]
GO
CREATE USER [guest] WITH DEFAULT_SCHEMA=[guest]
GO
CREATE USER [dbo] FOR LOGIN [HOME-BIX5T88DR6\Administrator] WITH DEFAULT_SCHEMA=[dbo]
GO

Q) What are the 2 types of roles available at db level ?
A)
Database Role
Application Role

Q) How to create a user ?
A)
USE [kali]
GO
CREATE USER [cat3] FOR LOGIN [sqlcat3] WITH DEFAULT_SCHEMA=[dbo]
GO

Q) How to alter a user's schema ?
A)
ALTER USER [cat3] WITH DEFAULT_SCHEMA=[dbo11]
GO

Q) How to drop a user ?
A)
DROP USER [cat3]
GO

Q) How to create a database role ?
A)
CREATE ROLE [readview] AUTHORIZATION [dbo]
GO
this means the user dbo here owns the role readview.

Q) How to change the authorization of a db role ?
A)
ALTER ROLE [readview] AUTHORIZATION [cat]
GO

Q) How to drop a database role ?
A)
DROP ROLE [readview]

Q) How to create a application role ?
A)
CREATE APPLICATION ROLE [app_role_1] WITH DEFAULT_SCHEMA = [dbo], PASSWORD = N'Gambler1!'
GO

Q) How to change the password and default schema for a application role ?
A)
ALTER APPLICATION ROLE [app_role_1] WITH DEFAULT_SCHEMA = [dbo11], PASSWORD = N'Gambler111!'
GO

Q) How to drop a application role ?
A)
DROP APPLICATION ROLE [app_role_1]

Q) How to add a user/member to a role ?
A)
EXEC sp_addrolemember N'db_datareader', N'cat3'
GO

Q) How to create a schema ?
A)
CREATE SCHEMA [sam] AUTHORIZATION [dbo]
GO

Q) I have the schemas and its associated tables in it as follows :
schema1-tab1
schema2-tab2
Now transfer the tab1 from schema1 into schema2.
A)
ALTER SCHEMA schema2 TRANSFER schema1.tab1;
GO

Q) How to drop a schema ?
A)
DROP SCHEMA schema2

Q) What is the difference between database role and application role ?
A)
Application Roles
Application roles are the SQL Server roles created to support the security needs of an application. Often database applications enforce their own security based on the application logic. For example, you can use application role with its own password to allow the particular user to obtain and modify any data only during specific hours. So, you can realize more complex security management within the application logic.

Database Roles

A database role applies to a single database. The database roles in a database apply to only that database and the objects within that database. Database roles are not used to grant or deny administrative access to objects.
There are three kinds of the database roles:
-Fixed Database Roles;
-Public Role;
-User-Defined Database Roles.

Fixed database roles are defined at the database level and exist in each database.
You cannot add, delete or modify fixed database roles. You can only add users as a member of a fixed database roles.

Fixed database roles are:
-db_owner
-db_accessadmin
-db_datareader
-db_datawriter
-db_ddladmin
-db_securityadmin
-db_backupoperator
-db_denydatareader
-db_denydatawriter

The public role is a special database role to which every database user belongs. The public role contain default access permissions for any user who can access the database. This database role cannot
be dropped.

Although the built-in database roles handle permissions for common database management tasks, it's likely that you will want to group users who have access to perform specific database functions.

Q) How to view all database principals ?
A)
select * from sys.database_principals

Q) How to view all database permissions ?
A)
select * from sys.database_permissions

Q) How to view all database role members ?
A)
select * from sys.database_role_members

Q) How to view the default db roles in any db ?
A)
exec sp_helprole

Q)

Auditing in sql server 2008 !

Q) What is the syntax for creating a server audit ?
A)
USE [master]

GO

CREATE SERVER AUDIT [audit1]
TO FILE
( FILEPATH = N'C:\audits'
,MAXSIZE = 100 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = ON
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)

GO

Q) Can a server audit be written only to a file ?
A)
No.We can store the audits not only in a file but also in either security log or application log.

Q) Give syntax/example of creating a server audit into a security log.
A)
USE [master]

GO

CREATE SERVER AUDIT [audit2_sec]
TO SECURITY_LOG
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)

GO

Q) Give syntax/example of creating a server audit into a application log.
A)
USE [master]

GO

CREATE SERVER AUDIT [audit3_app]
TO APPLICATION_LOG
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = SHUTDOWN
)

GO

Q) Are the server audits once created enabled by default ?
A) No.We need to enable them after creating it with the alter statement.

Q) How to enable a server audit ?
A) Use the below t-sql statement :
GO
ALTER SERVER AUDIT audit1
WITH (STATE = ON);

Q) How to Change a server audit name ?
A)
USE master
GO
ALTER SERVER AUDIT audit1
WITH (STATE = OFF);
GO
ALTER SERVER AUDIT audit1
MODIFY NAME = audit1_Old;
GO
ALTER SERVER AUDIT audit1_Old
WITH (STATE = ON);
GO

Q) What is the pre-requisite for performing any alter statement on server audits ?
A)
Turn the server audit state to off,perform the alter statement and then turn it back on.

Q) How to drop a server audit ?
A)
ALTER SERVER AUDIT audit1_Old
WITH STATE = OFF;
GO
DROP SERVER AUDIT audit1_Old;
GO


Q) How to view all the active/enabled server audits in an instance ?
A)
select * from sys.dm_server_audit_status

Q) How to view all the existing server audits in an instance ?
A)
select * from sys.server_audits

Q) How to view all the existing server audits that are configured to store audit records into a file system ?
A)
select * from sys.server_file_audits

Q) How to create a server audit specification ?
A)
USE [master]

GO

CREATE SERVER AUDIT SPECIFICATION [su_spec_01]
FOR SERVER AUDIT [audit1]
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP)

GO

Q) Explain various sql server audit action groups.
A)
successful_login_group
failed_login_group
login_change_password_group
logout_group

server_object_change_group
server_object_ownership_change_group
server_principal_change_group
server_principal_impersonation_group
server_role_member_change_group
server_operation_group
server_state_change_group
server_permission_change_group
server_object_permission_change_group

database_role_member_change_group
database_operation_group
database_change_group
database_object_change_group
database_principal_change_group
database_principal_impersonation_group
database_ownership_change_group
database_object_ownership_change_group
database_permission_change_group
database_object_permission_change_group
database_object_access_group
application_role_change_password_group

schema_object_change_group
schema_object_ownership_change_group
schema_object_permission_change_group
schema_object_access_group

backup_restore_group
dbcc_group
trace_change_group
broker_login_group
database_mirroring_login_group

audit_change_group

Q) Is server audit specification enabled by default ?
A) No.

Q) How to enable server audit specification ?
A)
ALTER SERVER AUDIT SPECIFICATION [su_spec_01]
WITH (STATE=ON)

Q) I have created a server audit specification by name [su_spec_01] for server audit [audit1] that contains 2 groups namely :server_object_change_group and database_change_group.Now I wanted to remove the sever_object_change_group from this specification.Give syntax.
A)
ALTER SERVER AUDIT SPECIFICATION [su_spec_01]
WITH (STATE=OFF)

ALTER SERVER AUDIT SPECIFICATION [su_spec_01]
DROP (SERVER_OBJECT_CHANGE_GROUP);

ALTER SERVER AUDIT SPECIFICATION [su_spec_01]
WITH (STATE=ON)

Q) How to drop a server audit specification ?
A)
ALTER SERVER AUDIT SPECIFICATION [su_spec_01]
WITH (STATE=OFF);
--first we need to disable it and then drop them.

DROP SERVER AUDIT SPECIFICATION [su_spec_01];

Q) How to view all the existing server audit specifications ?
A)
select * from sys.server_audit_specifications

Q) How to view all the active/enabled server audit specifications ?
A)
select * from sys.server_audit_specifications.
here,is_state_enabled column will tell us if its enabled or disabled.
enabled-1
disabled-0

Q) How to know all the groups configured for server audit specifications ?
A)
select * from sys.server_audit_specification_details

Q) How to know the list of every audit action that can be reported in the audit log and every audit action group that can be configured as part of SQL Server Audit ?
A)
select * from sys.dm_audit_actions.
This returns a row for every audit action that can be reported in the audit log and every audit action group that can be configured as part of SQL Server Audit.

Q) How to read information from the audited log file ?
A)
select * from sys.fn_get_audit_file('C:\audits\*.sqlaudit',default,default);

Q) There is a column called "class_type" in the dynamic fn "sys.fn_get_audit_file".Now how to find this class_type description of this class_type column ?
A)
select * from sys.dm_audit_class_type_map
example :
class_type class_type_desc securable_class_desc
DB DATABASE DATABASE

This actually returns a table that maps the class_type field in the audit log to the class_desc field in sys.dm_audit_actions.


Q) How to create a database audit specification ? Give an example.
A)
CREATE DATABASE AUDIT SPECIFICATION [db_audit_spec_01]
FOR SERVER AUDIT [audit2_sec]
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DELETE ON OBJECT::[dbo].[tab] BY [dba1]),
ADD (SELECT,INSERT ON OBJECT::[dbo].[tab] BY [dba1]);

GO

Remember before creating a database audit specification,we should have already created a server audit(here its audit2_sec).

Q) Is database audit specification enabled by default ?
A) No.

Q) How to enable a database audit speification ?
A)
ALTER DATABASE AUDIT SPECIFICATION [db_audit_spec_01]
WITH (STATE=ON)

Q) I have by mistake added the following to db audit specification [db_audit_spec_01].Now I want to remove this.Give syntax.
(SELECT,INSERT ON OBJECT::[dbo].[tab] BY [dba1]);
A)

ALTER DATABASE AUDIT SPECIFICATION [db_audit_spec_01]
WITH(STATE=OFF);

ALTER DATABASE AUDIT SPECIFICATION [db_audit_spec_01]
DROP (SELECT,INSERT ON OBJECT::[dbo].[tab] BY [dba1]);

ALTER DATABASE AUDIT SPECIFICATION [db_audit_spec_01]
WITH(STATE=ON);

Q) How to drop a database audit specification ?
A)
ALTER DATABASE AUDIT SPECIFICATION [db_audit_spec_01]
WITH(STATE=OFF);

DROP DATABASE AUDIT SPECIFICATION [db_audit_spec_01];

Q) How to view all the existing database audit specifications in an instance ?
A)
select * from sys.database_audit_specifications

Q) How to view all the active/enabled database audit specifications in an instance ?
A)
select * from sys.database_audit_specifications
here the column "is_state_enabled" tells us if this is enabled or disabled.
enabled-1
disabled-0

Q) How to know all the groups configured for a database audit specification ?
A)
select * from sys.database_audit_specification_details