Can I use a single stored procedure to operate on different schemas based on the executing user
You could probably use dynamic SQL,

CREATE PROCEDURE [StoredProcedures].[Insert_Table1]
AS

DECLARE @sql nvarchar(max)=N'
INSERT INTO table1 VALUES (NEWID());
INSERT INTO table1 SELECT col1 FROM StaticTable;
';

EXECUTE sys.sp_executesql @sql;


However, you should be aware that this is problematic for several reasons. The most obvious ones are:


If you're duplicating the entire schema for every user, there's probably something seriously wrong with your database design. Instead of giving users their own schemas, you should design permissions into the table (with a user column and row-level security),
It breaks ownership chaining (the user will now need permissions to the base tables, as opposed to inheriting rights from the stored procedure),
You need to manage SQL injection if your stored procedure accepts parameters that go into the SQL statement (preferably using parameterization),
Dynamic SQL has some other effects with regards to parameterization and performance that you should be aware of if you're dealing with larger OLTP-style loads.