sp_grantdbaccess (T-SQL)

Adds a security account in the current database for a Microsoft® SQL Server™ login or Microsoft Windows NT® user or group, and enables it to be granted permissions to perform activities in the database.

Syntax

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

Arguments

[@loginame =] 'login'

Is the name of the login for the new security account in the current database. Windows NT groups and users must be qualified with a Windows NT domain name in the form Domain\User, for example LONDON\Joeb. The login cannot already be aliased to an account in the database. login is sysname, with no default.

[@name_in_db =] 'name_in_db' [OUTPUT]

Is the name for the account in the database. 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.

Return Code Values

0 (success) or 1 (failure)

Remarks

SQL Server usernames can contain from 1 to 128 characters, including letters, symbols, and numbers. However, usernames cannot:

• Contain a backslash character (\).
• Be NULL, or an empty string (‘‘).

The security account must be granted access to the current database before it can use the database. Only accounts in the current database can be managed using sp_grantdbaccess. To remove an account from a database, use sp_revokedbaccess.

A security account for guest can be added if it does not already exist in the current database, and the login is also guest.

The sa login cannot be added to a database.

sp_grantdbaccess cannot be executed from within a user-defined transaction.

Permissions

Only members of the db_accessadmin or db_owner fixed database roles can execute sp_grantdbaccess.

Examples

This example adds an account for the Windows NT user Corporate\GeorgeW to the current database and gives it the name Georgie.

EXEC sp_grantdbaccess 'Corporate\GeorgeW', 'Georgie'

See Also