/* 
This script creates a new application role named "EQuIS SQL Form" (if it does not already exist); 
then grants VIEWER permission to that role. 

After the role is created, add users to the role via Enterprise, and they should then 
have permission to run the EQuIS SQL Form in Professional (even though they are not an admin user). 

NOTE: Publish the EQuIS SQL Form prior to executing this script. 
      If it is not published, permissions will not be granted to the role, 
      and this script will need to be executed again (i.e. after the form is published). 
*/ 
DECLARE @euid INT 
      , @sql_form VARCHAR(50) = 'EQuIS SQL Form' 
      , @sql_form_obj_name VARCHAR(50) = 'EarthSoft.Forms.Library._42804.SqlForm' 
      , @perm_viewer INT = 4 
      , @obj_typ_mod INT = -128 
      , @grant INT = 1 
      , @sys_id INT = -1; 

-- Add the "EQuIS SQL Form" application role to [st_role], if it does not exist. 
IF NOT EXISTS(SELECT * FROM dbo.st_role WHERE role_name = @sql_form) 
BEGIN 
  EXEC equis.get_ids 1, @euid OUTPUT; 
  INSERT INTO dbo.st_role (role_id, role_name) 
  VALUES (@euid, @sql_form); 
END; 
ELSE 
BEGIN 
  SELECT @euid = role_id 
  FROM dbo.st_role 
  WHERE role_name = @sql_form; 
END; 

-- Apply Viewer permissions to the "EQuIS SQL Form" role. 
WITH auth_needed AS 
( SELECT r.role_id AS grantee_id 
    , @perm_viewer AS permission_id 
    , m.module_id AS object_euid 
    , @grant AS permission_type 
    , @sys_id AS grantor_id 
  FROM dbo.st_role r, dbo.st_module m 
  WHERE r.role_id = @euid 
    AND m.[name] = @sql_form 
    AND m.[object_name] = @sql_form_obj_name 
) 
INSERT INTO dbo.xt_auth_acl (grantee_id, permission_id, object_euid, permission_type, grantor_id) 
SELECT n.grantee_id, n.permission_id, n.object_euid, n.permission_type, n.grantor_id 
FROM auth_needed n 
WHERE NOT EXISTS 
  ( SELECT * FROM dbo.xt_auth_acl x 
    WHERE n.grantee_id = x.grantee_id 
      AND n.permission_id = x.permission_id 
      AND n.object_euid = x.object_euid 
      AND n.permission_type = x.permission_type 
  );
GO