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

Permissions

Only a System Security Officer can execute sp_displayaudit.

See also

System procedures sp_audit

Utilities bcp