SQL Shield Code Generation Functions.

SQL Shield 6 introduced 3 functions helping to automate code generation and protection.

Important: By default those functions are not created by installer. There is a file called toolprocs.sql in installation folder which contains those functions. They are created automatically in the database when you use the GUI to encrypt objects. The GUI runs that script to create functions. You can customize it accordingly to your requirements.

You may customize the toolprocs.sql or functions in the database. They will not be re-created.

sqlshield_out_proc @name [, @password], [@gendrop], [@bindlic]

Function outputs specified object's definition in encrypted format. The resulting statement can be run to create object on the server with installed SQL Shield.

Example

Command: exec sqlshield_out_proc 'MySecureFunc'

output:

CREATE FUNCTION MySecureFunc ()
returns @retContactInformation TABLE (
FirstName NVARCHAR(50) NULL,
 LastName NVARCHAR(50) NULL )
WITH /*sqlshield6proc*/ ENCRYPTION
AS/*WRi3FiiCRtrp//kr1JM6XShuSl3McIgh2ZT2+GgLVV5haxKxhVeI77tFr5dNfHIZ
/zF/KAw08tmgh8VR3ZRTIw6TSpVS/UlFVVusimLnUdcfLNqgI1st8asYZT1m95Kk
jDfO/R3Xu5zfKFgh8k7LVjbhZDCMUmDNQGGTQ55OCPmIaqifc9MzJoL/5CkP7vs7
UWJSyY+T/sZAGwWl+zqtRWg/kOjT3bGPhON5HKEe4zSJVPQu8NPNO9cKsGzGRQmZ
xAvtCYEwYz3ZVAcpFHm77l2TYBWGQTa7EwsGglK+VkgFMMbLKWG4eNT1RVY0S9VC
vKR6PBHcLDnA3wOAFRJHzzwkAgypWi6wmb+M3S5L3Do= */
begin--<*>
return--<*>
end--<*>

Parameters:

@name  nvarchar(255)  source object name. It can be stored procedure, view, user defined function, table or database trigger.

@password varchar(255) optional parameter. That password can be used later to decrypt the object. If no password specified then decryption is not available.

@gendrop bit optional parameter. By default is 0, When set  to 1 , the procedure generates SQL statements to DROP object before creation.

@bindlic bit optional parameter. By default is 0, When set  to 1 , the procedure is encrypted with the dependency on licensee name. The procedure will not decrypt if licensee name is changed.

New object's content returned as result set. In case of errors nothing returned.

sqlshield_proc_type @name

Defines encryption type of the object.

Parameters:

name nvarchar(255)  source object name. It can be stored procedure, view, user defined function, table or database trigger.

Function returns INT value specifying: -1 - error, 0 - object is unencrypted, 1 - standard encryption , 2 - SQL Shield encryption.

sqlshield_encrypt_proc @name [, @password], [@bindlic]

Encrypts object in-place with SQL Shield encryption.

name nvarchar(255) source object name. It can be stored procedure, view, user defined function, table or database trigger.

@password varchar(255) optional parameter. That password can be used later to decrypt the object. If no password specified then decryption is not available.

@bindlic bit optional parameter. By default is 0, When set  to 1 , the procedure is encrypted with the dependency on licensee name. The procedure will not decrypt if licensee name is changed. For example if the database is transferred to another server.

Below are examples of how to encrypt all objects in the database at once:

The following script will encrypt in-place all user's objects: views, stored procedures, functions and triggers.

DECLARE @id int = -2147483648
DECLARE @name sysname
declare @execline nvarchar(4000)
WHILE(1 = 1)
BEGIN
  SELECT @id = MIN([object_id])
  FROM sys.all_objects WHERE [object_id] > @id and is_ms_shipped=0 and type in ('P','V','TF','TR','FN','IF')  and OBJECTPROPERTY([object_id], N'IsEncrypted') = 0
  IF @id IS NULL BREAK
  SELECT @name=[name] 
  FROM sys.all_objects WHERE @id = [object_id]
  set @execline='exec dbo.sqlshield_encrypt_proc @name='''+@name+''''
  execute (@execline)
END

The following script will encrypt and generate secure script for all user's objects: views, stored procedures, functions and triggers. Objects in the database are unchanged.

DECLARE @id int = -2147483648
DECLARE @name sysname
declare @execline nvarchar(4000)
WHILE(1 = 1)
BEGIN
  SELECT @id = MIN([object_id])
  FROM sys.all_objects WHERE [object_id] > @id and is_ms_shipped=0 and type in ('P','V','TF','TR','FN','IF')  and OBJECTPROPERTY([object_id], N'IsEncrypted') = 0
  IF @id IS NULL BREAK
  SELECT @name=[name] 
  FROM sys.all_objects WHERE @id = [object_id]
  set @execline='exec dbo.sqlshield_output_proc @gendrop=1, @name='''+@name+''''
  execute (@execline)
END