USP_CREATE_CONSTIT_SECURITY_ATTR_VIEW

CREATE procedure dbo.USP_CREATE_CONSTIT_SECURITY_ATTR_VIEW

@ID uniqueidentifier,@NAME NVARCHAR(100),@CHANGEAGENTID uniqueidentifier

 with execute as owner as
    set nocount on;

    declare @SQL nvarchar(max);
    declare @IDSTRING nchar(36);

    set @IDSTRING = convert(nchar(36), @ID);

    set @SQL = 'create view dbo.V_CONSTIT_SECURITY_ATTR_' + replace(@IDSTRING,'-','_') + ' with schemabinding' + nchar(13);
    set @SQL = @SQL + 'as' + nchar(13);
    set @SQL = @SQL + 'select CONSTITUENTID AS ID from dbo.CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT' + nchar(13);
    set @SQL = @SQL + 'where CONSTIT_SECURITY_ATTRIBUTEID = ' + nchar(39) + @IDSTRING + nchar(39) + ';';

    exec (@SQL);

  --bez 2/25/08 due to a bug with sql server selecting the wrong index we are no longer going to create this clustered index

    --set @SQL = 'create unique clustered index IX_V_CONSTIT_SECURITY_ATTR_' + replace(@IDSTRING,'-','_') + '_ID' + nchar(13);

    --set @SQL = @SQL + 'on dbo.V_CONSTIT_SECURITY_ATTR_' + replace(@IDSTRING,'-','_') + ' ([ID]) on IDXGROUP;';


    --exec (@SQL);



    set @SQL = 'grant select on dbo.V_CONSTIT_SECURITY_ATTR_' + replace(@IDSTRING,'-','_') + nchar(13);
    set @SQL = @SQL + ' to BBAPPFXSERVICEROLE';

    exec (@SQL);

    set @SQL = 'grant select on dbo.V_CONSTIT_SECURITY_ATTR_' + replace(@IDSTRING,'-','_') + nchar(13);
    set @SQL = @SQL + ' to BBAPPFXREPORTROLE';

    exec (@SQL);


    DECLARE @CONSTITUENTRECORDTYPEID uniqueidentifier;

    select
                @CONSTITUENTRECORDTYPEID = [ID]
            from
                dbo.[RECORDTYPE]
            where
                [NAME] = 'Constituent';

    insert into dbo.[IDSETREGISTER]
                (
                    [NAME],
                    [DESCRIPTION],
                    [RECORDTYPEID],
                    [DBOBJECTNAME],
                    [OBJECTTYPE],
                    [STATIC],
                    [USEINQUERYDESIGNER]
                    ,[ADDEDBYID]
                    ,[CHANGEDBYID]
                )
                values
                (
                    @NAME + ' (Security Group)',
                    'Records in Constituent Security Group ' + @NAME ,
                    @CONSTITUENTRECORDTYPEID,
                    'V_CONSTIT_SECURITY_ATTR_' + replace(@IDSTRING,'-','_'),
                    0,
                    0,
                    1
                    ,@CHANGEAGENTID
                    ,@CHANGEAGENTID
                );