[ @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.

0 (success) or 1 (failure)

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

Requires ownership of the database.

A. Adding a database user

EXEC sp_adduser 'Vidur', 'Vidur', 'Recruiting'

B. Adding a database user with the same login ID

EXEC sp_adduser 'Arvind'

C. Adding a database user with a different name than its server-level login

EXEC sp_adduser 'BjornR', 'Bjorn', 'Production'

Reference

Help and Information