USP_REPORT_SYSTEMROLE_DENIEDCODETABLEENTRIES

            CREATE procedure dbo.USP_REPORT_SYSTEMROLE_DENIEDCODETABLEENTRIES
            (
                @SYSTEMROLEID uniqueidentifier
            )
            with execute as owner
            as
                set nocount on;

                declare @DBTABLENAME nvarchar(100);
                create table #results(
                    CATEGORY nvarchar(100) collate database_default,
                    CODETABLENAME nvarchar(100)  collate database_default,
                    CODETABLEENTRYNAME nvarchar(100)  collate database_default
                );

                declare SECURABLECODETABLE_CURSOR cursor local fast_forward for
                select distinct 
                    DBTABLENAME 
                from 
                    dbo.CODETABLECATALOG 
                inner join 
                    dbo.SYSTEMROLEPERM_CODETABLEENTRY on CODETABLECATALOG.ID = SYSTEMROLEPERM_CODETABLEENTRY.CODETABLECATALOGID
                where 
                    SYSTEMROLEID = @SYSTEMROLEID;

                open SECURABLECODETABLE_CURSOR;

                fetch next from SECURABLECODETABLE_CURSOR into @DBTABLENAME;

                while @@FETCH_STATUS = 0 begin
                    declare @SQL nvarchar(max);

                    set @SQL = 'insert into #results(CATEGORY,CODETABLENAME,CODETABLEENTRYNAME) 
                                select 
                                    CODETABLECATEGORY.DESCRIPTION as CATEGORY,
                                    CODETABLECATALOG.UINAME,
                                    CT.DESCRIPTION as CODETABLEENTRYNAME
                                from dbo.' + @DBTABLENAME + ' CT 
                                inner join dbo.SYSTEMROLEPERM_CODETABLEENTRY on CT.ID = SYSTEMROLEPERM_CODETABLEENTRY.CODETABLEENTRYID and SYSTEMROLEPERM_CODETABLEENTRY.SYSTEMROLEID = @ROLEID
                                inner join dbo.CODETABLECATALOG on SYSTEMROLEPERM_CODETABLEENTRY.CODETABLECATALOGID = CODETABLECATALOG.ID
                                inner join dbo.CODETABLECATEGORY on CODETABLECATALOG.CATEGORYID = CODETABLECATEGORY.ID
                                order by CT.DESCRIPTION'

                    exec sp_executesql @SQL, N'@ROLEID uniqueidentifier', @ROLEID = @SYSTEMROLEID;

                    fetch next from SECURABLECODETABLE_CURSOR into @DBTABLENAME;
                end

                close SECURABLECODETABLE_CURSOR;
                deallocate SECURABLECODETABLE_CURSOR;

                select CATEGORY,CODETABLENAME,CODETABLEENTRYNAME from #results;

                drop table #results;