I've had some success using temp tables to pass multi-row data into stored procs. This has worked well since SQL Server 2000 and is for passing smaller numbers of rows.

After struggling with user-based security we've transitioned to proc-based security. This works well when the schema of the proc and tables are the same -- except in cases of dynamic...