sp_displayaudit

Description

Displays the status of audit options.

Syntax

sp_displayaudit ["procedure" | "object" | "login" | "database" | "global" | 
	"default_object" | "default_procedure" [, "name"]]

Parameters

procedure: displays the status of audit options for the specified stored procedure or trigger. If you do not specify a value for name, sp_displayaudit displays the active audit options for all procedures and triggers in the current database.

object: displays the status of audit options for the specified table or view. If you do not specify a value for name, sp_displayaudit displays the active audit options for all tables and views in the current database.

login: displays the status of audit options for the specified user login. If you do not specify a value for name, sp_displayaudit displays the active audit options for all logins in the master database.

database: displays the status of audit options for the specified database. If you do not specify a value for name, sp_displayaudit displays the active audit options for all databases on the server.

global: displays the status of the specified global audit option. If you do not specify a value for name, sp_displayaudit displays the active audit options for all procedures and triggers in the current database.

default_object: displays the default audit options that will be used for any new table or view created on the specified database. If you do not specify a value for name, sp_displayaudit displays the default audit options for all databases with active default audit settings.

default_procedure: displays the default audit options that will be used for any new procedure or trigger created on the specified database. If you do not specify a value for name, sp_displayaudit displays the default audit options for all databases with active default audit settings.

name

is the information for the specified parameter, as described in the following table:

Parameter	Value for name
procedure	Procedure or trigger name
object	Table or view name
login	User login
database	Database name
global	Global audit option
default_object	Database name
default_procedure	Database name

Examples

Example 1

When no parameter is specified, the status of each category and all auditing options is displayed:

sp_displayaudit

Procedure/Trigger    Audit Option   Value Database
 -----------------   -------------- ----- ---------------------
 dbo.sp_altermessage exec_procedure on    sybsystemprocs 
 dbo.sp_help         exec_procedure on    sybsystemprocs 
 dbo.sp_who          exec_procedure on    sybsystemprocs
No databases currently have default sproc/trigger auditing enabled.
No objects currently have auditing enabled.
No databases currently have default table/view auditing enabled.
No logins currently have auditing enabled.
No databases currently have auditing enabled.

Option Name                    Value
------------------------------ ------------------------------
adhoc                          off 
dbcc                           off 
disk                           off 
errors                         off 
login                          off 
logout                         off 
navigator_role                 off 
oper_role                      off 
replication_role               off 
rpc                            off 
sa_role                        off 
security                       off 
sso_role                       off

Example 2

When no procedure name is specified, the status of all procedure audit options is displayed:

sp_displayaudit "procedure"

Procedure/Trigger    Audit Option   Value Database
 -----------------   -------------- ----- ---------------------
 dbo.sp_altermessage exec_procedure on    sybsystemprocs
 dbo.sp_help         exec_procedure on    sybsystemprocs
 dbo.sp_who          exec_procedure on    sybsystemprocs

Example 3

When you specify a name for the procedure, only the status of that procedure is displayed:

sp_displayaudit "procedure", "sp_who"

Procedure/Trigger Audit Option    Value Database
----------------- --------------- ----- ----------------------
dbo.sp_who        exec_procedure  on    sybsystemprocs

Example 4

When no global audit option is specified, the status of all global audit options is displayed:

sp_displayaudit "global"

Option Name                    Value
------------------------------ ------------------------------
adhoc                          off
dbcc                           off
disk                           off
errors                         off
login                          off
logout                         off
navigator_role                 off
oper_role                      off
replication_role               off
rpc                            off
sa_role                        off
security                       off
sso_role                       off

Usage

sp_displayaudit displays the status of audit options.

The following table shows the valid auditing options for each parameter:

Object type parameter	Valid auditing options
procedure	exec_procedure, exec_trigger
object	delete, func_obj_access, insert, reference, select, update
login	all, cmdtext, table_access, view_access
database	alter, bcp, bind, create, dbaccess, drop, dump, func_dbaccess, grant, load, revoke, setuser, truncate, unbind
global	adhoc, dbcc, disk, errors, login, logout, navigator_role, oper_role, replication_role, rpc, sa_role, security, sso_role
default_object	delete, func_obj_access, insert, reference, select, update
default_procedure	exec_procedure, exec_trigger

You cannot specify a value for name unless you first specify an object type parameter.
See the System Administration Guide for information on setting up auditing.

Permissions

Only a System Security Officer can execute sp_displayaudit.