sp_adduser (Transact-SQL)

Adds a new user to the current database.

Transact-SQL Syntax Conventions

Syntax

sp_adduser [ @loginame = ] 'login' 
        [ , [ @name_in_db = ] 'user' ] 
        [ , [ @grpname = ] 'role' ] 

Arguments

• [ @loginame = ] 'login'
  Is the name of the SQL Server login or Windows login. login is a sysname, with no default. login must be an existing SQL Server login or Windows login.

• [ @name_in_db = ] 'user'
  Is the name for the new database user. user is a sysname, with a default of NULL. If user is not specified, the name of the new database user defaults to the login name. Specifying user gives the new user a name in the database different from the server-level login name.

• [ @grpname = ] 'role'
  Is the database role of which the new user becomes a member. role is sysname, with a default of NULL. role must be a valid database role in the current database.

Return Code Values

0 (success) or 1 (failure)

Remarks

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

Permissions

Requires ownership of the database.

Examples

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'

See Also

Reference

Security Stored Procedures (Transact-SQL)
sys.server_principals (Transact-SQL)
sp_addrole (Transact-SQL)
CREATE USER (Transact-SQL)
sp_dropuser (Transact-SQL)
sp_grantdbaccess (Transact-SQL)
sp_grantlogin (Transact-SQL)
System Stored Procedures (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance