Sql-server – use a single stored procedure to operate on different schemas based on the executing user

application-designdatabase-designmulti-tenantschemasql server

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..

Best Answer

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.
Related Question