Can I use a single stored procedure to operate on different schemas based on the executing user

I have thousands of schemas with same set of tables, Each user has a default schema. But I dont want to create copies of a single stored procedure in every schema. Can a single stored procedure access the tables of user specific schema.

I have created the test projects to test this but it is throwing error as the table not found

```
create Database PermissionsTest

-- Create user1 and UserSchema1 and assign permissions
CREATE LOGIN [User1] WITH PASSWORD=N'User1'
GO
CREATE USER [User1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA=[UserSchema1]
GO
ALTER LOGIN [User1] Enable
go
CREATE SCHEMA [UserSchema1] AUTHORIZATION [User1]
GO

-- Create user2 and UserSchema2 and assign permissions
CREATE LOGIN [User2] WITH PASSWORD=N'User2'
GO
CREATE USER [User2] FOR LOGIN [User2] WITH DEFAULT_SCHEMA=[UserSchema2]
GO
ALTER LOGIN [User2] Enable
go
CREATE SCHEMA [UserSchema2] AUTHORIZATION [User2]
GO

-- Create StoredProcedure Schema and creating the role to execute on this schema
CREATE ROLE [ExecuteSprocsOnStoredProcsSchema] AUTHORIZATION [dbo]
GO

ALTER AUTHORIZATION ON role::ExecuteSprocsOnStoredProcsSchema TO User1;
GO

Create Schema [StoredProcedures] AUTHORIZATION [ExecuteSprocsOnStoredProcsSchema]
Go

EXEC sp_addrolemember N'ExecuteSprocsOnStoredProcsSchema', N'User1'
GO
EXEC sp_addrolemember N'ExecuteSprocsOnStoredProcsSchema', N'User2'
Go

-- GRANT Execute ON SCHEMA :: StoredProcedures TO ExecuteSprocsOnStoredProcsSchema

GRANT CONNECT TO [User1]
Grant Connect to [User2]

grant SELECT ON SCHEMA::[dbo] TO [User1]
GO
grant SELECT ON SCHEMA::[dbo] TO [User2]
GO
----------------- Database data side changes ---------------
USE PermissionsTest
GO
CREATE TABLE [dbo].StaticTable NOT NULL,
[col1] [int] NULL,
PRIMARY KEY CLUSTERED ([pkcol])
)
GO

insert into StaticTable values (420);

-- drop table [UserSchema2].[Table1]

CREATE TABLE [UserSchema1].Table1 NOT NULL,
[col1] varchar NULL,
PRIM

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.

StackExchange Database Administrators Q#153249, answer score: 3