sp_dropalias (Transact-SQL)

Removes an alias that links a user in the current database to a SQL Server login.

Important

sp_dropalias provides compatibility with earlier versions of SQL Server. This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.SQL Server provides roles and the ability to grant permissions to roles as an alternative to using aliases.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_dropalias [ @loginame = ] 'login'

Arguments

  • [ @loginame = ] 'login'
    Is the name of the SQL Server login, Windows login, or Windows group from which the alias is to be removed. login is sysname, with no default. login must already exist.

Return Code Values

0 (success) or 1 (failure)

Remarks

Aliases enable logins to assume the identity of a user in a database, thereby gaining the permissions of that user while working in that database.

When the alias is removed, the login can no longer perform the activities associated with the user whose alias the login assumed in the current database.

sp_dropalias cannot be executed within a user-defined transaction.

Permissions

Requires membership in the db_owner fixed database role.

Examples

The following example removes the alias to SQL Server login Victoria from the current database.

EXEC sp_dropalias 'Victoria'