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.
sp_grantdbaccess [@loginame =] 'login'
[,[@name_in_db =] 'name_in_db' [OUTPUT]]
[@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)
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 ('').
Note The value for 'login' can include a backslash character to separate the domain name from the user name, but it cannot be used as part of the user name itself.
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.
Only members of the sysadmin fixed server role, the db_accessadmin and db_owner fixed database roles can execute sp_grantdbaccess.
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'