• I use option 2 all the time using a login-less Database User. This way the procedure executes as a Database User with elevated permissions but no one can ever connect to the database as that user directly meaning it only exists for purposes of running dynamic SQL. This technique only works however if you are only making calls to tables within the same database, i.e. not doing any cross-database calls in your Dynamic SQL.

    USE [YourDatabase];

    GO

    CREATE USER [DynamicSqlRunner] WITHOUT LOGIN;

    GO

    CREATE PROC dbo.WhateverProc (@ParameterName VARCHAR(100))

    WITH EXECUTE AS 'DynamicSqlRunner'

    AS

    BEGIN

    -- proc code goes here

    SELECT 1

    END

    GO

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato