Your account: {A_Col1}
Your name: {A_Col2}
Your billing address: {B1_B_Col1}
Your delivery address: {B2_B_Col1}

SELECT A_Col1, A_Col2, B1.B_Col1 AS [B1_B_Col1], B2.B_Col1 AS [B2_B_Col1]
FROM TableA AS A
     JOIN TableB AS B1
         ON B1.ID = A.ID_B1
     JOIN TableB AS B2
         ON B2.ID = A.ID_B2
WHERE A.ColN = @ParamN
ORDER BY A_Col1, B1.B_Col2, B2.B_Col3

SELECT @strSQL = 'SELECT ' + @SelectList + '
FROM TableA AS A
     JOIN TableB AS B1
         ON B1.ID = A.ID_B1
     JOIN TableB AS B2
         ON B2.ID = A.ID_B2
WHERE A.ColN = @ParamN
ORDER BY A_Col1, B1.B_Col2, B2.B_Col3'

EXEC sp_ExecuteSQL @strSQL, N'@ParamN int', @ParamN

CREATE PROCEDURE dbo.MySproc
WITH EXECUTE AS OWNER
AS
...

EXEC | EXECUTE } 
        ( { @string_variable | [ N ]'tsql_string' } [ + ...n ] )
    [ AS { LOGIN | USER } = ' name ' ]

SELECT
--{SELECT_LIST_START}
     A_Col1, A_Col2, B1.B_Col1 AS [B1_B_Col1], B2.B_Col1 AS [B2_B_Col1]
--{SELECT_LIST_END}
FROM TableA AS A
     JOIN TableB AS B1
         ON B1.ID = A.ID_B1
     JOIN TableB AS B2
         ON B2.ID = A.ID_B2
WHERE A.ColN = @ParamN
ORDER BY A_Col1, B1.B_Col2, B2.B_Col3

quote:

---

Originally posted by X002548


In any Case, that connection ID (SQL Server security right) is going to HAVE to have read access to the tables

---

CREATE PROCEDURE MySproc
     @ParamN int
WITH EXECUTE AS 'MyUser'
AS

    SELECT @strSQL = 'SELECT ' + @SelectList + '
    FROM TableA AS A
         JOIN TableB AS B1
             ON B1.ID = A.ID_B1
         JOIN TableB AS B2
             ON B2.ID = A.ID_B2
    WHERE A.ColN = @ParamN
    ORDER BY A_Col1, B1.B_Col2, B2.B_Col3'

    EXEC sp_ExecuteSQL @strSQL, N'@ParamN int', @ParamN

CREATE LOGIN MyUser WITH PASSWORD = 'MyPassword'
GO
USE MyDatabase
GO
CREATE USER MyUser FOR LOGIN MyUser
GO 
EXEC sp_addrolemember 'MyRole', 'MyUser'
EXEC sp_addrolemember 'db_datareader', 'MyUser'
EXEC sp_addrolemember 'db_datawriter', 'MyUser'

CREATE USER MyUser WITHOUT LOGIN

WITH EXECUTE AS 'MyUser'