Cursor within a cursor for database dependent permissions

Question

It would seem that my code should work, however testing indicates that all the results from the inner cursor are based on the current database at execution of the script and not dependent upon the USE statement within the script. WHAT DID I FORGET?

DECLARE @Debug      BIT          = 1
DECLARE @newgrp VARCHAR(100) = 'ChangeTrakingViewableRole'
DECLARE @obj        VARCHAR(100)
DECLARE @tsql       VARCHAR(900)
DECLARE @tsql2      VARCHAR(900)
DECLARE @msg        VARCHAR(900)
DECLARE @SchName    VARCHAR(55)
DECLARE @TblName    sysname
IF @Debug = 'TRUE' PRINT 'Debuging ON'
IF COALESCE(@newgrp,'') = '' 
    BEGIN
        PRINT 'There was no DatabaseRole, User or Group Specified to take the place of the Public Role'
        SET NOEXEC ON
    END
ELSE 
    BEGIN
        DECLARE DbCursor CURSOR FOR
            SELECT 'USE '+DB_NAME(database_id) FROM sys.change_tracking_databases
        OPEN DbCursor
        FETCH NEXT FROM DbCursor INTO @obj
        WHILE @@Fetch_Status = 0 
            BEGIN
                SET @tsql2 = @obj+'; '
                RAISERROR (@tsql2, 0, 1) WITH NOWAIT
                EXEC sp_sqlexec @tsql2
                -----------Commands within this next section are all database dependent
                BEGIN   --GRANT [VIEW CHANGE TRACKING] TO Change Tracking Enabled Tables
                    IF NOT EXISTS (SELECT name FROM sys.database_principals where name = @newgrp)
                        BEGIN
                            SET @tsql = N'CREATE ROLE '+@newgrp+' AUTHORIZATION [dbo]'
                            IF @Debug = 'TRUE'
                                BEGIN 
                                    SET @Msg = @tsql
                                    RAISERROR (@Msg, 0, 1) WITH NOWAIT
                                END 
                            ELSE
                                BEGIN 
                                    EXEC sp_sqlexec @tsql
                                END 
                        END
                    DECLARE TblCursor CURSOR FOR
                        SELECT sch.name, tbl.name
                        FROM sys.change_tracking_tables chg
                        JOIN sys.tables                 tbl ON chg.object_id=tbl.object_id
                        JOIN sys.schemas                sch ON tbl.schema_id=sch.schema_id
                        ORDER BY sch.name, tbl.name
                    OPEN TblCursor
                    FETCH NEXT FROM TblCursor INTO @SchName,@TblName
                    WHILE @@FETCH_STATUS = 0
                        BEGIN
                            SET @tsql = 'GRANT VIEW CHANGE TRACKING ON ['+@SchName+'].['+@TblName+'] TO '+@newgrp
                            IF @Debug = 'TRUE'
                                BEGIN 
                                    SET @Msg = @tsql
                                    RAISERROR (@Msg, 0, 1) WITH NOWAIT
                                END 
                            ELSE
                                BEGIN 
                                    EXEC sp_sqlexec @tsql
                                END 
                            FETCH NEXT FROM TblCursor INTO @SchName,@TblName
                        END
                    CLOSE TblCursor
                    DEALLOCATE TblCursor
                END
                FETCH NEXT FROM DbCursor INTO @obj
            END
        CLOSE DbCursor
        DEALLOCATE DbCursor
    END

Answer 1

The USE statements in your outer cursor are not doing anything for the statements generated aftwerward because they're being executed independently. When your outer cursor executes the USE it is just valid for the scope of the first EXEC sp_sqlexec call; it doesn't change the database context of the overall script. The context under which the rest of your script runs is still that of the overall script, meaning those statements will get run in the current database every time.

Bascially, you need to change this to generate a single script with the entirety of what you want to execute within the dynamic db context top to bottom, with the USE at the top, and then execute that whole thing in a single call to EXEC or sp_executesql.