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