Displays the status of audit options.
sp_displayaudit ["procedure" | "object" | "login" | "database" | "global" | "default_object" | "default_procedure" [, "name"]]
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.
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.
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.
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.
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.
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.
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.
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 |
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
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
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
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
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, encryption_key, func_dbaccess, grant, load, revoke, setuser, truncate, unbind |
global |
adhoc, dbcc, disk, errors, login, logout, navigator_role, oper_role, replication_role, rpc, keycustodian_role, 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.
Only a system security officer can execute sp_displayaudit.
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 |
|
Documents See the System Administration Guide for information on setting up auditing.
System procedures sp_audit
