| title | sp_adduser (Transact-SQL) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 03/14/2017 | ||
| ms.prod | sql-non-specified | ||
| ms.reviewer | |||
| ms.suite | |||
| ms.technology |
|
||
| ms.tgt_pltfrm | |||
| ms.topic | language-reference | ||
| f1_keywords |
|
||
| dev_langs |
|
||
| helpviewer_keywords |
|
||
| ms.assetid | 61a40eb4-573f-460c-9164-bd1bbfaf8b25 | ||
| caps.latest.revision | 27 | ||
| author | BYHAM | ||
| ms.author | rickbyh | ||
| manager | jhubbard |
[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx_md]
Adds a new user to the current database.
Important
[!INCLUDEssNoteDepFutureAvoid] Use CREATE USER instead.
| Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssKatmai] through current version). |
Transact-SQL Syntax Conventions
sp_adduser [ @loginame = ] 'login'
[ , [ @name_in_db = ] 'user' ]
[ , [ @grpname = ] 'role' ]
[ @loginame = ] 'login'
Is the name of the [!INCLUDEssNoVersion] login or Windows login. login is a sysname, with no default. login must be an existing [!INCLUDEssNoVersion] 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.
The following example adds the database user Vidur to the existing Recruiting role in the current database, using the existing [!INCLUDEssNoVersion] login Vidur.
EXEC sp_adduser 'Vidur', 'Vidur', 'Recruiting';
The following example adds user Arvind to the current database for the [!INCLUDEssNoVersion] login Arvind. This user belongs to the default public role.
EXEC sp_adduser 'Arvind';
The following example adds [!INCLUDEssNoVersion] 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';
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)