That's the equivalent of a CROSS JOIN:

SELECT 'UPDATE'
      ,s.login_name
      ,i.batch_id
  FROM INSERTED AS i
  CROSS...

Or, just in case there might be: '64.0 MB,restricted growth to 2097152.0 MB'

SELECT * FROM #tbl_datafile_list WHERE Autogrow LIKE '%[^A-Za-z]restricted[^A-Za-z]%'

Triggers don't necessarily suck at performance, it depends on how they are written.  Neither does CDC perform all that poorly; in fact, I think it's extremely unlikely you could write...

;WITH cte AS (
    SELECT @@SERVERNAME AS ServerName,
           ISNULL(b.database_name, d.name) AS DatabaseName,
  ...

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...