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

Question

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](
   [pkcol] [int] IDENTITY(1,1) NOT NULL,
   [col1] [int] NULL,
PRIMARY KEY CLUSTERED ([pkcol])
)
GO

insert into StaticTable values (420);

-- drop table [UserSchema2].[Table1]

CREATE TABLE [UserSchema1].[Table1](
   [pkcol] [int] IDENTITY(1,1) NOT NULL,
   [col1] [varchar](max) NULL,
PRIMARY KEY CLUSTERED ([pkcol])
)
GO

CREATE TABLE [UserSchema2].[Table1](
   [pkcol] [int] IDENTITY(1,1) NOT NULL,
   [col1] [varchar](max) NULL,
PRIMARY KEY CLUSTERED ([pkcol])
)
GO

Create Procedure [StoredProcedures].[Insert_Table1]
as
begin
Insert into table1 values (newID());
Insert into table1 select col1 from StaticTable;
end

after setting up the system for two users and the stored propcedure Insert_Table1

When I try to execute the stored procedure by User1 I get this error

Msg 208, Level 16, State 1, Procedure Insert_Table1, Line 6 Invalid object name 'table1'.

but when I run the queries separately as user1 it gets executed without any problem.

I know while executing the stored procedure the stored procedure temperarily takes the permissions of the Schema owener of that Stored procedure

I have created the schema with authorization to the role assigned to user1 already.

Create Schema [StoredProcedures] AUTHORIZATION [ExecuteSprocsOnStoredProcsSchema]

Am I missing anything.. Is there any way to make it work? Or is there any way that a single stored procedure can be used over multiple schemas..

Answer 1

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.

Answer 2

I do not believe that what you are trying to accomplish is possible, at least not without an extra, over-complicated layer of Dynamic SQL.

Since you have already gone through the trouble of creating unique Logins and Schemas, you might want to consider one of the following approaches:

1. Accept the separate Schema concept and create the Stored Procedures (and Functions?) in each Schema. You mentioned (quote from now deleted duplicate question):

   I want to have same set of stored procedures to be executed on each schemas to avoid the maintainance overhead for each schema.

   but what maintenance overhead? You already have the same Tables per each Schema, so your release/rollout process already needs to account for applying Table / Constraint / Trigger / Index changes to ALL Schemas, so what harm is there in adding the "code" objects to the mix (and again, Triggers are already copied to each Schema since they exist per Table).

2. Move away from using Schemas as the separation and create a Database per each client. This would allow for all objects to be identical. You just need to deploy to each Database instead of to each Schema. But needing to do the release / rollout / deployment across a series of Databases isn't really any different than needing to do it across a series of Schemas (something that you already need to do in your current model).