sp_adduser System Stored Procedure

Adds a new user to the current database.

Syntax

sp_adduser login_id [, username [, grpname]]

where

login_id: Is the user's login ID as found in the name column of master.dbo.syslogins.
username: Specifies a name for this user in the current database. Omitting this parameter causes the username to default to the user's login ID.
grpname: Identifies an existing group in the database. If you don't specify a group, the user becomes a member of the default group public. If you want to add a user to a group, you must supply a value for the username parameter. (The username can be the same as login_id.)
A user can be a member of only one group, in addition to public. Every user is a member of the default group, public. The group must already exist in the database.

Remarks

The sp_adduser system stored procedure treats the combination of a guest login ID and user guest as a special case. In earlier releases, a non-special guest user could be created within a user-defined database. SQL Server 6.0 prevents this from happening, in the following way:

If a login name of guest is specified with the sp_adduser system stored procedure and a guest login exists and the username is not specified as guest, a user with the suid of the login name guest will be added.
```
sp_adduser guest, dbuser1
```
If the username guest is specified and the login name specified is not guest, an error message is returned, stating the guest user cannot be mapped to a login.
```
sp_adduser loginid1, guest
```
If a login name of guest is specified and the username specified is guest or not supplied, then the user created within the database will be the special guest user. If the guest user already exists, an error message will be returned.
```
sp_adduser guest, guest
```

The database owner executes the sp_adduser system stored procedure to add a login ID to the sysusers table of the current database, enabling the user to access the current database under his or her own name.

Specifying a username parameter gives the new user a name in the database different from his or her login ID on SQL Server. The ability to assign a different name to a user on SQL Server and in a database is provided as a convenience. This is not an alias as provided by the sp_addalias system stored procedure¾ the user is simply given a different name, not mapped to the identity and permissions of another user as with sp_addalias.

To access a database, a user must be listed in sysusers (by using sp_adduser) or mapped to another user in sysalternates (by using sp_addalias), or there must be a guest entry in sysusers.

Examples

A. Add a User

The SQL Server login ID Victoria has already been added to SQL Server with sp_addlogin. This example adds Victoria to the database. Victoria will be known in the current database by the username Victoria and belong to the group fort_mudge (previously created with sp_addgroup).

sp_adduser Victoria, Victoria, fort_mudge

B. Add a Username with the Same Login ID

This example adds Margaret to the database. Her database username is the same as her SQL Server login ID, and she belongs to the default group public.

sp_adduser Margaret

C. Add a User Who Uses a Different Username

This example adds Haroldq to the database. When Haroldq uses the current database, his name is Harold. He belongs to the fort_mudge group.

sp_adduser Haroldq, Harold, fort_mudge

Permission

Execute permission defaults to the database owner.

Tables Used

master.dbo.syslogins, sysalternates, sysusers

GRANT	sp_dropgroup
REVOKE	sp_helpuser
sp_addalias	USE
sp_addgroup	xp_grantlogin
sp_changegroup	xp_logininfo
sp_dropalias	xp_revokelogin