sp_addalias (Transact-SQL)
Maps a login to a user in a database.
Important
sp_addalias provides compatibility with earlier versions of SQL Server. This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. As an alternative to using aliases, the SQL Server 2005 Database Engine provides roles and the ability to grant permissions to roles. For more information, see CREATE USER (Transact-SQL) and CREATE ROLE (Transact-SQL).
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
sp_addalias [ @loginame = ] 'login' , [ @name_in_db = ] 'alias_user'
Arguments
- [ @loginame = ] 'login'
Is the name of the login to which an alias will be assigned. login is sysname with no default. login must be a valid SQL Server login or Microsoft Windows user with permission to connect to an instance of the SQL Server. login cannot already exist or be assigned as an alias to an existing user in the database.
- [ @name_in_db = ] 'alias_user'
Is the name of the user to which the login is mapped. alias_user is sysname with no default. alias_user must be a user in the database in which the login is aliased. When you map to a Windows user, specify the name by which it is known in the database.
Return Code Values
0 (success) or 1 (failure)
Remarks
A login can be mapped to users in any database. Execute sp_addalias only in the database in which the user must have an alias. When users connect to an instance of the Database Engine by using the login parameter, they can perform activities in the database under the permissions applied to alias_user.
Note
You cannot create an alias for the sa login.
A login can access a database under the following circumstances:
- The login has an associated user account in the database.
- The login has a user alias in the database, which the database owner or a member of the sysadmin fixed server role has added by using sp_addalias.
- The guest account has been enabled in the database.
The sp_addalias stored procedure cannot be executed from a user-defined transaction.
The following table shows several system stored procedures that can be used in conjunction with sp_addalias.
| Stored procedure | Description |
|---|---|
sp_helplogins |
Returns a list of valid login values. |
sp_helpuser |
Returns a list of valid alias_user values in the database in which the login is used. |
sp_dropalias |
Removes an alias mapping. |
Permissions
Requires membership in the db_owner fixed database role.
Examples
The following example shows how to map SQL Server login Victoria to an existing user (Albert) in the AdventureWorks database.
USE AdventureWorks;
EXEC sp_addalias 'Victoria', 'Albert' ;
See Also
Reference
Security Stored Procedures (Transact-SQL)
CREATE USER (Transact-SQL)
CREATE ROLE (Transact-SQL)
sp_dropalias (Transact-SQL)
sp_helplogins (Transact-SQL)
sp_helpuser (Transact-SQL)
System Stored Procedures (Transact-SQL)
sp_grantdbaccess (Transact-SQL)