sp_dropalias

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. The SAP ASE 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

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

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage any user` privilege.
Disabled	With granular permissions disabled, you must be the database owner, a user with sso_role, or 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