sp_addalias System Stored Procedure

Maps one user to another in a database.

Syntax

sp_addalias login_id, username

where

login_id

Is the master.dbo.syslogins login ID of the user who wants an alternate identity in the current database. An alias allows a SQL Server user to be known in a database as another user. If the user with this login_id exists in the database's sysusers table, SQL Server won't find the user's alias identity, since it checks sysusers before checking sysalternates.

username

Specifies the database user with whom the first user will be linked. This name must exist in both master.dbo.syslogins and in the sysusers table of the current database.

Remarks

Executing sp_addalias maps one user (login_id) to another (login_id) in the current database. The mapping is shown in sysalternates, where the two users' suids are connected.

A report on any users mapped to a specified user can be generated with the sp_helpuser system stored procedure if you give the specified login ID as a parameter.

When a user tries to use a database, SQL Server checks sysusers to see if the login ID is listed there. If the login ID is not there, it then checks sysalternates. If an entry (the user's suid) is found there. and it is mapped to a database user's suid (altsuid in sysalternates table), the first user is treated as the second user while using the database.

There are three ways that a user can be authorized to use a database (assuming the user is listed in master.dbo.syslogins).

• The user can be listed in the sysusers table of that database. (New database users are added by the database owner with the sp_adduser system stored procedure.)
• The user can have an alias in the database, which has been added by the database owner with the sp_addalias system stored procedure.
• The user can use the database as guest if there is a guest entry in the database's sysusers table.

Example

In this example, there is a user named Albert in the current database's sysusers table and a login ID for a user ID Victoria in master.dbo.syslogins. The sp_addalias system stored procedure allows Victoria to use the current database and to be known as Albert in that database.

sp_addalias Victoria, Albert

Permission

Execute permission defaults to the database owner.

Tables Used

master.dbo.syslogins, sysalternates, sysusers

See Also