drop default

Syntax

drop default [owner.]default_name 
	[, [owner.]default_name] ...

default_name – is the name of an existing default. Execute sp_help to display a list of existing defaults. Specify the owner’s name to drop a default of the same name owned by a different user in the current database. The default value for owner is the current user.

Example 1 – Removes the user-defined default datedefault from the database:
```
drop default datedefault
```

You cannot drop a default that is currently bound to a column or to a user-defined datatype. Use sp_unbindefault to unbind the default before you drop it.
You can bind a new default to a column or user-defined datatype without unbinding its current default. The new default overrides the old one.
When you drop a default for a NULL column, NULL becomes the column’s default value. When you drop a default for a NOT NULL column, an error message appears if users do not explicitly enter a value for that column when inserting data.

See also sp_help, sp_helptext, sp_unbindefault in Reference Manual: Procedures.

ANSI SQL – Compliance level: Transact-SQL extension.

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

Setting	Description
Enabled	With granular permissions enabled, you must be the default owner or a user with `drop any default` privilege.
Disabled	With granular permissions disabled, you must be the default owner or a user with sa_role.

drop default permission defaults to the owner of the default and is not transferable.

Values in event and extrainfo columns of sysaudits are:

Information	Values
Event	31
Audit option	drop
Command or access audited	drop default
Information in `extrainfo`	Roles – current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – NULL Proxy information – original login name, if set proxy is in effect