USP_DATAFORMTEMPLATE_EDIT_ADHOCQUERYFOLDERPERMISSIONS

The save procedure used by the edit dataform template "Ad-Hoc Query Folder Permissions Edit Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS	bit	IN	Apply the following as default permissions to all subfolders
@SECURITYLEVEL	tinyint	IN	This folder is available to
@SYSTEMROLELIST	xml	IN	System role list
@QUERYDEFAULTALLOWALLUSERSTORUN	bit	IN	Allow all users to run this query
@QUERYDEFAULTALLOWALLUSERSTOEDIT	bit	IN	Allow all users to edit this query
@APPLYQUERYDEFAULTSTOEXISTINGQUERIES	bit	IN	Apply default query permissions to existing queries
@QUERYDEFAULTRUNROLES	xml	IN	Query default run roles
@QUERYDEFAULTEDITROLES	xml	IN	Query default edit roles
@SMARTQUERYDEFAULTALLOWALLUSERSTORUN	bit	IN	Allow all users to run this query
@SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT	bit	IN	Allow other users to modify this query
@SMARTQUERYDEFAULTRUNROLES	xml	IN	Smart Query default run roles
Definition

Copy

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_ADHOCQUERYFOLDERPERMISSIONS
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS bit,
  @SECURITYLEVEL tinyint,
  @SYSTEMROLELIST xml,
  @QUERYDEFAULTALLOWALLUSERSTORUN bit,
  @QUERYDEFAULTALLOWALLUSERSTOEDIT bit,
  @APPLYQUERYDEFAULTSTOEXISTINGQUERIES bit,
  @QUERYDEFAULTRUNROLES xml,
  @QUERYDEFAULTEDITROLES xml,
  @SMARTQUERYDEFAULTALLOWALLUSERSTORUN bit,
  @SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT bit,
  @SMARTQUERYDEFAULTRUNROLES xml
)
as

  set nocount on;

  if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

  declare @CURRENTDATE datetime
  set @CURRENTDATE = getdate()

  declare @FOLDERSTOUPDATE table
  (
    FOLDERID uniqueidentifier
  )

  insert into @FOLDERSTOUPDATE (FOLDERID) values (@ID)

  if @APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS = 1
  begin
    -- Build the list of subfolders
    with RECURSIVECTE as
    (
      select
        ID, 
        PARENTFOLDERID
      from dbo.ADHOCQUERYFOLDER
      where
        PARENTFOLDERID = @ID

      union all

      select
        ADHOCQUERYFOLDER.ID,
        ADHOCQUERYFOLDER.PARENTFOLDERID
      from dbo.ADHOCQUERYFOLDER
      inner join RECURSIVECTE on ADHOCQUERYFOLDER.PARENTFOLDERID = RECURSIVECTE.ID
    )
    insert into @FOLDERSTOUPDATE (FOLDERID)
    select ID
    from RECURSIVECTE
    where ID <> @ID
  end

  begin try
    update dbo.ADHOCQUERYFOLDER set
      APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS = @APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    where ID = @ID

    update dbo.ADHOCQUERYFOLDER set
      SECURITYLEVELCODE = @SECURITYLEVEL,
      QUERYDEFAULTALLOWALLUSERSTORUN = @QUERYDEFAULTALLOWALLUSERSTORUN,
      QUERYDEFAULTALLOWALLUSERSTOEDIT = @QUERYDEFAULTALLOWALLUSERSTOEDIT,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE,
      SMARTQUERYDEFAULTALLOWALLUSERSTORUN = @SMARTQUERYDEFAULTALLOWALLUSERSTORUN,
      SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT = @SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT
    where ID in (select FOLDERID from @FOLDERSTOUPDATE)

    declare @contextCache varbinary(128);
    set @contextCache = CONTEXT_INFO();
    set CONTEXT_INFO @CHANGEAGENTID;

    -- Always clear the folder permissions.  Even though the folders
    -- may be re-inserted for Selected Roles, we can't do updates
    -- since we may be updating multiple folders and so the IDs won't
    -- be relevant.
    delete from dbo.SYSTEMROLEPERM_ADHOCQUERYFOLDER
    where ADHOCQUERYFOLDERID in (select FOLDERID from @FOLDERSTOUPDATE)

    if @SECURITYLEVEL = 1 -- Selected roles
    begin
      insert into dbo.SYSTEMROLEPERM_ADHOCQUERYFOLDER
      (
        ADHOCQUERYFOLDERID,
        SYSTEMROLEID,
        GRANTORDENY,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
      )
      select
        FOLDERSTOUPDATE.FOLDERID,
        T.c.value('(SYSTEMROLEID)[1]','uniqueidentifier') AS 'SYSTEMROLEID',
        T.c.value('(GRANTORDENY)[1]','bit') AS 'GRANTORDENY',
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
      from @SYSTEMROLELIST.nodes('/SYSTEMROLELIST/ITEM') T(c)
      cross join @FOLDERSTOUPDATE FOLDERSTOUPDATE
      where 
        T.c.value('(GRANTORDENY)[1]','tinyint') in (0, 1)
    end


    delete from dbo.ADHOCQUERYFOLDERDEFAULTQUERYROLE
    where ADHOCQUERYFOLDERID in (select FOLDERID from @FOLDERSTOUPDATE)

    if @QUERYDEFAULTALLOWALLUSERSTORUN = 0
      insert into dbo.ADHOCQUERYFOLDERDEFAULTQUERYROLE
      (
        ADHOCQUERYFOLDERID,
        SYSTEMROLEID,
        GRANTORDENY,
        PERMISSIONTYPECODE,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
      )
      select
        FOLDERSTOUPDATE.FOLDERID,
        T.c.value('(SYSTEMROLEID)[1]','uniqueidentifier') AS 'SYSTEMROLEID',
        T.c.value('(GRANTORDENY)[1]','bit') AS 'GRANTORDENY',
        0, -- Run
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
      from @QUERYDEFAULTRUNROLES.nodes('/QUERYDEFAULTRUNROLES/ITEM') T(c)
      cross join @FOLDERSTOUPDATE FOLDERSTOUPDATE
      where 
        T.c.value('(GRANTORDENY)[1]','tinyint') in (0, 1)

    if @QUERYDEFAULTALLOWALLUSERSTOEDIT = 0
      insert into dbo.ADHOCQUERYFOLDERDEFAULTQUERYROLE
      (
        ADHOCQUERYFOLDERID,
        SYSTEMROLEID,
        GRANTORDENY,
        PERMISSIONTYPECODE,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
      )
      select
        FOLDERSTOUPDATE.FOLDERID,
        T.c.value('(SYSTEMROLEID)[1]','uniqueidentifier') AS 'SYSTEMROLEID',
        T.c.value('(GRANTORDENY)[1]','bit') AS 'GRANTORDENY',
        1, -- Edit
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
      from @QUERYDEFAULTEDITROLES.nodes('/QUERYDEFAULTEDITROLES/ITEM') T(c)
      cross join @FOLDERSTOUPDATE FOLDERSTOUPDATE
      where 
        T.c.value('(GRANTORDENY)[1]','tinyint') in (0, 1)

    if @SMARTQUERYDEFAULTALLOWALLUSERSTORUN = 0
      insert into dbo.ADHOCQUERYFOLDERDEFAULTQUERYROLE
      (
        ADHOCQUERYFOLDERID,
        SYSTEMROLEID,
        GRANTORDENY,
        PERMISSIONTYPECODE,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
      )
      select
        FOLDERSTOUPDATE.FOLDERID,
        T.c.value('(SYSTEMROLEID)[1]','uniqueidentifier') AS 'SYSTEMROLEID',
        T.c.value('(GRANTORDENY)[1]','bit') AS 'GRANTORDENY',
        2, -- RunSmartQuery
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
      from @SMARTQUERYDEFAULTRUNROLES.nodes('/SMARTQUERYDEFAULTRUNROLES/ITEM') T(c)
      cross join @FOLDERSTOUPDATE FOLDERSTOUPDATE
      where 
        T.c.value('(GRANTORDENY)[1]','tinyint') in (0, 1)

    if @APPLYQUERYDEFAULTSTOEXISTINGQUERIES = 1
    begin
      update dbo.ADHOCQUERY set
        SECURITYLEVEL = case @QUERYDEFAULTALLOWALLUSERSTORUN 
                  when 1 then 0 -- All roles
                  else 1 -- Selected roles
                end,
        SECURITYLEVELEDIT = case @QUERYDEFAULTALLOWALLUSERSTOEDIT
                    when 1 then 0 -- All roles
                    else 1 -- Selected roles
                  end,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        FOLDERID in (select FOLDERID from @FOLDERSTOUPDATE)

      update dbo.SMARTQUERYINSTANCE set
        SECURITYLEVEL = case @SMARTQUERYDEFAULTALLOWALLUSERSTORUN 
                  when 1 then 0 -- All roles
                  else 1 -- Selected roles
                end,
        OTHERSCANMODIFY = case @SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT
                    when 1 then 1 -- Currently just a boolean, but allow for specifying specific roles in the future.
                    else 0
                  end,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        FOLDERID in (select FOLDERID from @FOLDERSTOUPDATE)

      -- Update query run permissions
      delete from dbo.SYSTEMROLEPERM_ADHOCQUERYINSTANCE
      where ADHOCQUERYID in 
        (
          select ADHOCQUERY.ID 
          from dbo.ADHOCQUERY
          inner join @FOLDERSTOUPDATE FOLDERSTOUPDATE on ADHOCQUERY.FOLDERID = FOLDERSTOUPDATE.FOLDERID)

      if @QUERYDEFAULTALLOWALLUSERSTORUN = 0
        insert into dbo.SYSTEMROLEPERM_ADHOCQUERYINSTANCE
        (
          ADHOCQUERYID,
          SYSTEMROLEID,
          GRANTORDENY,
          ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select
          INCLUDEDQUERY.ID,
          T.c.value('(SYSTEMROLEID)[1]','uniqueidentifier') AS 'SYSTEMROLEID',
          T.c.value('(GRANTORDENY)[1]','bit') AS 'GRANTORDENY',
          @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from @QUERYDEFAULTRUNROLES.nodes('/QUERYDEFAULTRUNROLES/ITEM') T(c)
        cross join
        (
          select ADHOCQUERY.ID 
          from dbo.ADHOCQUERY
          inner join @FOLDERSTOUPDATE FOLDERSTOUPDATE on ADHOCQUERY.FOLDERID = FOLDERSTOUPDATE.FOLDERID
        ) as INCLUDEDQUERY
        where 
          T.c.value('(GRANTORDENY)[1]','tinyint') in (0, 1)

      -- Update query edit permissions
      delete from dbo.SYSTEMROLEPERM_ADHOCQUERYINSTANCEEDIT
      where ADHOCQUERYID in 
        (
          select ADHOCQUERY.ID 
          from dbo.ADHOCQUERY
          inner join @FOLDERSTOUPDATE FOLDERSTOUPDATE on ADHOCQUERY.FOLDERID = FOLDERSTOUPDATE.FOLDERID)

      if @QUERYDEFAULTALLOWALLUSERSTOEDIT = 0
        insert into dbo.SYSTEMROLEPERM_ADHOCQUERYINSTANCEEDIT
        (
          ADHOCQUERYID,
          SYSTEMROLEID,
          GRANTORDENY,
          ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select
          INCLUDEDQUERY.ID,
          T.c.value('(SYSTEMROLEID)[1]','uniqueidentifier') AS 'SYSTEMROLEID',
          T.c.value('(GRANTORDENY)[1]','bit') AS 'GRANTORDENY',
          @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from @QUERYDEFAULTEDITROLES.nodes('/QUERYDEFAULTEDITROLES/ITEM') T(c)
        cross join
        (
          select ADHOCQUERY.ID 
          from dbo.ADHOCQUERY
          inner join @FOLDERSTOUPDATE FOLDERSTOUPDATE on ADHOCQUERY.FOLDERID = FOLDERSTOUPDATE.FOLDERID
        ) as INCLUDEDQUERY
        where 
          T.c.value('(GRANTORDENY)[1]','tinyint') in (0, 1)

      -- Update smart query instance run permissions
      delete from dbo.SYSTEMROLEPERM_SMARTQUERYINSTANCE
      where SMARTQUERYID in 
        (
          select SMARTQUERYINSTANCE.ID 
          from dbo.SMARTQUERYINSTANCE
          inner join @FOLDERSTOUPDATE FOLDERSTOUPDATE on SMARTQUERYINSTANCE.FOLDERID = FOLDERSTOUPDATE.FOLDERID)

      if @SMARTQUERYDEFAULTALLOWALLUSERSTORUN = 0
        insert into dbo.SYSTEMROLEPERM_SMARTQUERYINSTANCE
        (
          SMARTQUERYID,
          SYSTEMROLEID,
          GRANTORDENY,
          ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select
          INCLUDEDQUERY.ID,
          T.c.value('(SYSTEMROLEID)[1]','uniqueidentifier') AS 'SYSTEMROLEID',
          T.c.value('(GRANTORDENY)[1]','bit') AS 'GRANTORDENY',
          @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from @SMARTQUERYDEFAULTRUNROLES.nodes('/SMARTQUERYDEFAULTRUNROLES/ITEM') T(c)
        cross join
        (
          select SMARTQUERYINSTANCE.ID 
          from dbo.SMARTQUERYINSTANCE
          inner join @FOLDERSTOUPDATE FOLDERSTOUPDATE on SMARTQUERYINSTANCE.FOLDERID = FOLDERSTOUPDATE.FOLDERID
        ) as INCLUDEDQUERY
        where 
          T.c.value('(GRANTORDENY)[1]','tinyint') in (0, 1)
    end

    if not @contextCache is null
      set CONTEXT_INFO @contextCache;
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
  end catch

return 0;