Chapter 1: System Procedures

sp_dropalias

Description

Removes the alias user name identity established with sp_addalias.

Syntax

sp_dropalias loginame [, force]

Parameters

loginame: is the name (in master.dbo.syslogins) of the user who was aliased to another user.

force: allows you to drop an alias even if it owns database objects.

Examples

Example 1

Assuming that “victoria” was aliased (for example, to the Database Owner) in the current database, this statement drops “victoria” as an aliased user from the database:

sp_dropalias victoria

Example 2

Drops the alias “harry,” which owns a procedure namelist. Adaptive Server drops the alias but issues a warning message:

sp_dropalias harry, force
Warning: You have forced the drop of the alias for login 'harry' which owns objects in the database. This may result in errors when those objects are accessed from or contain references to another database.
Alias user dropped.(return status = 0)

Usage

Executing the sp_dropalias procedure deletes an alternate suid mapping for a user from the sysalternates table.
When a user’s alias is dropped, he or she no longer has access to the database for which the alias was created.
You can drop the alias of a user who owns objects in the database. You do not need to first drop the objects before dropping the login.

Permissions

Only the Database Owner or a System Administrator can execute sp_dropalias.

Auditing

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