dbd_add_login

exec dbd_add_login @login sysname, @passwd sysname =N'' , @save bit = 0 | 1 (default)

This function is automatically created in encrypted database at the moment of encryption. The database must be initially unlocked with OPEN SYMMETRIC KEY DBDX statement. Call this procedure in the context of encrypted database.

Security notice: Information is stored in key files. If someone copy the database and key files to another server he could get access by using 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".

Adds login and current database to the list of "automatic logins". Specified logins get access to encrypted databases automatically, but with some restrictions. Changes applied only to new connections and does not affect existing connections. It works only for "real" login and does not work with impersonated user (EXECUTE AS)

The function does not replace existing SQL Server authentication mechanism. It only grants access to the database for given login and password that passed existing SQL Server authentication.

Parameters:

@login - any login name. Not verified and may be actually created later. It may be SQL login, Windows login or Windows Group. If @login is a Windows Group, SQL principal with this name must already exist.

@passwd - if @passwd is specified then current login's password as verified with that password. Access is denied if passwords are different. If no password specified, then access 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 next change or SQL Server server restart.

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 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 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