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'
exec dbd_add_login @login='BUILTIN\users'
exec dbd_add_login @login='SERV\mike'

Older Versions

If the database was encrypted with an older DbDefence version, the function can be created manually:

  
create procedure dbd_add_login (
	@login sysname, 
	@passwd sysname =N'' , 
	@save bit =1
	)
as
declare @id int
select @id=db_id()
declare  @group  int
if exists (select * from sys.server_principals 
	 where name=@login and type='G')  
begin
	set @group=1
	set @passwd=N''
end
else
	set @group=0
exec master.._dbd_add_login @dbid=@id, 
			   @login=@login,
			   @passwd=@passwd,
			   @save=@save,
			   @group=@group

Note: to create objects in the existing encrypted database, you need to unlock it first.

Remarks

To see the list of auto logins, run dbd_list_login, to delete dbd_del_login