sp_grantdbaccess (Transact-SQL)

Artikel
2024-07-17

Applies to: SQL Server

Adds a database user to the current database.

Important

This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CREATE USER instead.

Transact-SQL syntax conventions

Syntax

sp_grantdbaccess
    [ @loginame = ] N'loginame'
    [ , [ @name_in_db = ] N'name_in_db' OUTPUT ]
[ ; ]

Arguments

[ @loginame = ] N'loginame'

The name of the Windows group, Windows login, or SQL Server login, to be mapped to the new database user. @loginame is sysname, with no default. 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 can't already be mapped to a user in the database.

[ @name_in_db = ] N'name_in_db' OUTPUT

The name for the new database user. @name_in_db is an OUTPUT parameter of type sysname. If not specified, @loginame is used. If specified as an OUTPUT variable with a value of NULL, @name_in_db is set to @loginame. @name_in_db must not already exist in the current database.

Return code values

0 (success) or 1 (failure).

Remarks

sp_grantdbaccess calls CREATE USER, which supports extra options. For information about creating database users, see CREATE USER. To remove a database user from a database, use DROP USER.

sp_grantdbaccess can't be executed within a user-defined transaction.

Permissions

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

Examples

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

CREATE USER Lolan FOR LOGIN [Edmonds\LolanSo];
GO

Dela via