sp_diskdefault

Specifies whether or not a database device can be used for database storage if the user does not specify a database device or specifies default with the create database or alter database commands.

Syntax

sp_diskdefault logicalname, {defaulton | defaultoff}

Parameters

Examples

Usage

There are additional considerations when using sp_diskdefault:
  • A default database device is one that is used for database storage by create database or alter database if the user does not specify a database device name or specifies the keyword default.

  • You can have multiple default devices. They are used in the order they appear in the master.dbo.sysdevices table (that is, alphabetical order). When the first default device is filled, the second default device is used, and so on.

  • When you first install SAP ASE, the master device is the only default database device.

    Note: Once you initialize devices to store user databases, use sp_diskdefault to turn off the master device’s default status. This prevents users from accidentally creating databases on the master device and simplifies recovery of the master database.
  • To find out which database devices are default database devices, execute sp_helpdevice.

See also alter database, create database, disk init in Reference Manual: Commands.

Permissions

The permission checks for sp_diskdefault differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be a user with manage disk privilege.

Disabled

With granular permissions disabled, you must be a user with sa_role.

Auditing

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

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • 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

Related reference
sp_helpdevice