dbd_add_login
exec dbd_add_login @login sysname, @passwd sysname =N'' , @save bit = 0 | 1 (default)
This function is automatically created in an encrypted database at the moment of encryption. The database must be initially unlocked with the OPEN SYMMETRIC KEY DBDX statement. Call this procedure in the context of the encrypted database.
Security notice: Information is stored in key files. If someone copies the database and key files to another server, he could get access by using the same login name. To avoid this, specify @passwd parameter.
Attention: if you test this feature with SQL Management Studio (SSMS), you need to restart SSMS after any change to see the result. SSMS caches connections even if you press "Disconnect".
Specified logins get access to encrypted databases automatically, but with some restrictions. Changes are applied only to new connections and do not affect existing connections. This works only for "real" login and does not work with impersonated user (EXECUTE AS).
The function does not replace the existing SQL Server authentication mechanism. It only grants access to the database for the given login and password that passed the existing SQL Server authentication.
Parameters:
@login - login name. It may be an SQL login, Windows login or Windows Group. If @login is a Windows Group, an SQL principal with this name must already exist.
@passwd - if @passwd is specified, then the login's password as verified with that password too. Access is denied if passwords are different. If no password is specified, then access is granted only by login name. Passwords can only be compared for SQL login.
@save - make permanent changes. Default set to 1 (yes). If set to 0, changes are not saved. It remains active until the next change or SQL Server restarts.
Doing the same with the GUI
You can do the same with the GUI before encrypting the database. However, the GUI does not set password dependency (@passwd).
Restrictions
Such automatic logins get full access to the database data, but can't change DbDefence settings for the database: backup mode or call dbd_add_login, dbd_add_ex.
Return Code Values
0 for success otherwise failure
Example
use [mydatabase] open symmetric key dbdx decryption by password='C00lPass!' -- let them access freely: exec dbd_add_login @login='sa' -- let login1 access the database only if he logged with the password mYpAssW0RD exec dbd_add_login @login='login1',@passwd='mYpAssW0RD' exec dbd_add_login @login='BUILTIN\users' exec dbd_add_login @login='SERV\mike'