Adds a new user to the current database.
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CREATE USER instead.
Applies to: SQL Server (SQL Server 2008 through current version).
sp_adduser will also create a schema that has the name of the user.
After a user has been added, use the GRANT, DENY, and REVOKE statements to define the permissions that control the activities performed by the user.
Use sys.server_principals to display a list of valid login names.
Use sp_helprole to display a list of the valid role names. When you specify a role, the user automatically gains the permissions that are defined for the role. If a role is not specified, the user gains the permissions granted to the default public role. To add a user to a role, a value for the user name must be supplied. (username can be the same as login_id.)
User guest already exists in every database. Adding user guest will enable this user, if it was previously disabled. By default, user guest is disabled in new databases.
sp_adduser cannot be executed inside a user-defined transaction.
You cannot add a guest user because a guest user already exists inside every database. To enable the guest user, grant guest CONNECT permission as shown:
GRANT CONNECT TO guest; GO
A. Adding a database user
The following example adds the database user Vidur to the existing Recruiting role in the current database, using the existing SQL Server login Vidur.
EXEC sp_adduser 'Vidur', 'Vidur', 'Recruiting';
B. Adding a database user with the same login ID
The following example adds user Arvind to the current database for the SQL Server login Arvind. This user belongs to the default public role.
EXEC sp_adduser 'Arvind';
C. Adding a database user with a different name than its server-level login
The following example adds SQL Server login BjornR to the current database that has a user name of Bjorn, and adds database user Bjorn to the Production database role.
EXEC sp_adduser 'BjornR', 'Bjorn', 'Production';