Or make the value column a sql_variant data type.

SQL may not evaluate things in the order you expect it to, so something like that could indeed be happening in certain circumstances.

;WITH cte AS (
    SELECT @@SERVERNAME AS ServerName,
           b.database_name AS DatabaseName,
   ...

Still, be sure you do not have some type of accidental join / cross join that will generate extra rows.

I'm not sure I understand correctly, nor what result you expect to see from the sample data, but maybe this(?), based on my reading of your requirements so far:

            
November 1, 2023 at 7:11 pm
            
#4319191

            
            
                                                
        

Sadly, without giving IMPERSONATE to the users, I cannot let...

Great, glad it helped.  You might want to mark my comment as the solution, to potentially help future readers.

Yeah, in the test environment, that's not surprising.

We're back to having to look at the query plans.

I would think you would want to use "EXEC AS" on the proc itself rather than inside the proc.

@zoggling:

There should have been 2 result sets.  Did the first query -- for missing indexes -- not return any rows?

I would think so.  If any objects have permissions set to "public", for example, I would think UserA could access them.

And naturally for contained dbs permissions would not require a...

DELETE in batches rather than all at once, as long as each DELETE won't require (up to) a full scan of the table to find the rows to delete.

If you...

Compress the data.  Look at both page compression and COMPRESS/DECOMPRESS for large char columns.

You might also look into data compression (both page compression and COMPRESS()/DECOMPRESS() for long text data).

Agreed.  Partition the data if you need to update stats on a very large table and you can separate active data from inactive data by partitioning.