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

Displays the status of each category and all auditing options when you do not specify a parameter:

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 
keycustodian_role              off 
navigator_role                 off 
oper_role                      off 
replication_role               off 
rpc                            off 
sa_role                        off 
security                       off 
sso_role                       off

Example 2

Displays the status of all procedure audit options when you do not specify a procedure name:

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

Displays only the status of the procedure when you specify a name for a procedure:

sp_displayaudit "procedure", "sp_who"
Procedure/Trigger Audit Option    Value Database
----------------- --------------- ----- ----------------------
dbo.sp_who        exec_procedure  on    sybsystemprocs

Example 4

Displays the status of all global audit options when you do not specify a global audit option:

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

Usage

Permissions

Only a System Security Officer can execute sp_displayaudit.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

Documents See the System Administration Guide for information on setting up auditing.

System procedures sp_audit

Utilities bcp