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

logicalname – is the logical name of the device as given in master.dbo.sysdevices.name. The device must be a database device rather than a dump device.
defaulton | defaultoff – defaulton designates the database device as a default database device; defaultoff designates that the specified database device is not a default database device.
Use defaulton after adding a database device to the system with disk init. Use defaultoff to change the default status of the master device (which is designated as a default device when SAP ASE is first installed).

Examples

Example 1 – The master device is no longer used by create database or alter database for default storage of a database:
```
sp_diskdefault master, defaultoff
```

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.

Setting	Description
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:

Information	Values
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