CODEWITHSUNDEEP
sqlsql-serversql-server-2016
StackMark
StackMark

Reputation: 15

Execute Procedure with SELECT from different database, without read/write rights on that different database

I have a role with execute on schema, and a user that should be able to execute every procedure that he see (under that schema), but those procedures are using tables/views from separate database that that user should not be able to see.

Here is working solution but only if all components (table and procedure) are in same database, and with queries from this link I know that there are no any denied access for user/role:

Maybe there is some role setup that I should Use? Below there is basic example what I want achieve:

USE [master];
GO
CREATE DATABASE [temporary1];
CREATE DATABASE [temporary2];
CREATE LOGIN [hero1] WITH PASSWORD = 'batman', CHECK_POLICY = OFF;

---------------

USE [temporary1];
GO
CREATE USER [hero1] FROM LOGIN [hero1];
CREATE TABLE [dbo].[test_table] ([id] INT);
INSERT [dbo].[test_table] VALUES(1);

---------------

USE [temporary2];
GO
CREATE USER [hero1] FROM LOGIN [hero1];

---------------

CREATE PROCEDURE [dbo].[inter_database_secret]
AS
BEGIN
    SELECT [id] FROM [temporary1].[dbo].[test_table];
END

---------------

GRANT EXECUTE ON [temporary2].[dbo].[inter_database_secret] TO [hero1]

---------------

EXECUTE AS USER = 'hero1';
GO
EXECUTE [temporary2].[dbo].[inter_database_secret];  --<---- Sad Error Here
REVERT;

---------------

USE [master];
GO
DROP DATABASE [temporary1];
DROP DATABASE [temporary2];
DROP LOGIN [hero1];

Upvotes: 1

Views: 924

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89141

Almost there. Two small issues. First, you have to explicitly configure cross-database ownership chaining. It's off by default. Second is that you have to impersonate hero1's login, not the database user. hero1 the database principal (user) doesn't have access to the other database. hero1 the server principal (login) does. So

USE [master];
GO
CREATE DATABASE [temporary1];
CREATE DATABASE [temporary2];
CREATE LOGIN [hero1] WITH PASSWORD = 'batman', CHECK_POLICY = OFF;

ALTER DATABASE [temporary1] SET DB_CHAINING ON;  
ALTER DATABASE [temporary2] SET DB_CHAINING ON;  
go

USE [temporary1];
GO
CREATE USER [hero1] FROM LOGIN [hero1];
CREATE TABLE [dbo].[test_table] ([id] INT);
INSERT [dbo].[test_table] VALUES(1);

---------------

USE [temporary2];
GO
CREATE USER [hero1] FROM LOGIN [hero1];

go

CREATE PROCEDURE [dbo].[inter_database_secret]
AS
BEGIN
    SELECT [id] FROM [temporary1].[dbo].[test_table];
END

go

GRANT EXECUTE ON [temporary2].[dbo].[inter_database_secret] TO [hero1]

go
use [temporary2]
go
EXECUTE AS login = 'hero1';
  EXECUTE [dbo].[inter_database_secret];  --<---- Happy Result Here
REVERT;

go

USE [master];
GO
DROP DATABASE [temporary1];
DROP DATABASE [temporary2];
DROP LOGIN [hero1];

Upvotes: 1

Related Questions