UFN_SECURITY_ADHOCQUERY_USERCANSAVEFORQUERYVIEWS

Returns a flag indicating the user has rights to all query views in a given ad-hoc query based on its current definition and the definition to which it is being updated.

Return

Return Type
bit

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@NEWQUERYDEFINITIONXML	xml	IN
@APPUSERID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.UFN_SECURITY_ADHOCQUERY_USERCANSAVEFORQUERYVIEWS(@ID uniqueidentifier,
    @NEWQUERYDEFINITIONXML xml,
    @APPUSERID uniqueidentifier)
returns bit
as
begin

    declare @RETURNVALUE bit;

    declare @EXISTINGID uniqueidentifier;
    declare @EXISTINGQUERYDEFINITIONXML xml;

    select @EXISTINGID = ADHOCQUERY.ID,
        @EXISTINGQUERYDEFINITIONXML = ADHOCQUERY.QUERYDEFINITIONXML
    from dbo.ADHOCQUERY 
    where ADHOCQUERY.ID = @ID;

    /* Check rights to query views used in the ad-hoc query definition passed in */
    set @RETURNVALUE = dbo.UFN_ADHOCQUERY_USERHASRIGHTSTOQUERYVIEWS(@APPUSERID, @NEWQUERYDEFINITIONXML);

    if (@RETURNVALUE = 1) and (not @EXISTINGID is null) and (convert(nvarchar(max), @NEWQUERYDEFINITIONXML) != convert(nvarchar(max),@EXISTINGQUERYDEFINITIONXML))
    begin
        declare @ISSYSADMIN bit;
        select @ISSYSADMIN = ISSYSADMIN from dbo.APPUSER where ID = @APPUSERID;

        /* Check rights to query views used in the ad-hoc query definition that already exists to ensure he's not removing fields from query views to which
        he doesn't have rights */
        set @RETURNVALUE = dbo.UFN_ADHOCQUERY_USERHASRIGHTSTOQUERYVIEWS(@APPUSERID, @EXISTINGQUERYDEFINITIONXML)
    end;

    return @RETURNVALUE;

end;