create default

The or replace clause allows you to replace a default's definition using create default.

Syntax

Changes are in bold.

create [or replace] [owner.] default_name           
as constant_expression   

Parameter Changes for create or replace default

Example

This example creates a default with a phone number defined as UNKNOWN:
create default phonedflt as "UNKNOWN"       

select object_id("phonedflt")      
-----------    
1001051571  
This default replaces the previously created default using the or replace clause. The phone number is changed, but the object ID of the default remains the same:
create or replace default phonedflt as "999-999-9999"       

select object_id("phonedflt")      
-----------    
1001051571  

Objects Dependent on Replaced Defaults

Procedures that access these columns will be recompiled when the default is replaced and the procedure is executed.

Permission Changes for create or replace default

Any user who impersonates the default owner through an alias or setuser cannot replace the default.

Changes for replacing a default are in bold.

Granular permissions enabled With granular permissions enabled, you must have the create default privilege. To create a default for another user, you must have the create any default privilege. You must be the default owner to replace the default.
Granular permissions disabled With granular permissions disabled, you must be the database owner, a user with sa_role, or have the create default privilege. You must be the default owner to replace the default.

Auditing Changes for create or replace default

Changes are in bold.

Event Audit Option Command or access audited Information in extrainfo
14 create create default
  • Roles – current active roles
  • Keywords or options – NULL
  • Previous value – NULL
  • Other information – NULL
  • Current value – NULL
  • Proxy information – original login name, if set proxy is in effect
  • or replace – for create or replace