sp_grantdbaccess (Transact-SQL)


Updated: June 10, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Adds a database user to the current database.

System_CAPS_ICON_important.jpg Important

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).

Topic link icon Transact-SQL Syntax Conventions

sp_grantdbaccess [ @loginame = ] 'login'  
    [ , [ @name_in_db = ] 'name_in_db' [ OUTPUT ] ]  

[ @loginame = ] 'login '
Is the name of the Windows group, Windows login or SQL Server login to be mapped to the new database user. Names of Windows groups and Windows logins must be qualified with a Windows domain name in the form Domain\login; for example, LONDON\Joeb. The login cannot already be mapped to a user in the database. login is a sysname, with no default.

[ @name_in_db=] 'name_in_db' [ OUTPUT]
Is the name for the new database user. name_in_db is an OUTPUT variable with a data type of sysname, and a default of NULL. If not specified, login is used. If specified as an OUTPUT variable with a value of NULL, @name_in_db is set to login. name_in_db must not already exist in the current database.

0 (success) or 1 (failure)

sp_grantdbaccess calls CREATE USER, which supports additional options. For information about creating database users, see CREATE USER (Transact-SQL). To remove a database user from a database, use DROP USER.

sp_grantdbaccess cannot be executed within a user-defined transaction.

Requires membership in the db_owner fixed database role or the db_accessadmin fixed database role.

The following example uses CREATE USER to add a database user for the Windows login Edmonds\LolanSo to the current database. The new user is named Lolan. This is the preferred method for creating a database user.

CREATE USER Lolan FOR LOGIN [Edmonds\LolanSo];  

Security Stored Procedures (Transact-SQL)
DROP USER (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions