I have a staging database [StageDB] and a production database [ProdDB] where data is initially imported from an API into the [StageDB] and then the data inserted or updated into the ProdDB.

I have a Windows authenticated Login to the instance named [User1], which has a Public Server Role and User Mapping to [StageDB] and [ProdDB] but no Memberships.

There is stored procedure on [ProdDB] named proc_update_documents.

ALTER PROCEDURE [dbo].[proc_update_documents]
AS

UPDATE [ProdDB].[dbo].[tbl_documents]
    SET
        [last_name] = D.[last_name],
        [first_name] = D.[first_name],
        [category] = D.[category]
FROM
        [StageDB].[dbo].[tbl_documents] D
WHERE
        D.[document_id] = [ProdDB].[dbo].[tbl_documents].[document_id];

INSERT INTO [ProdDB].[dbo].[tbl_documents]
(
    [document_id],
    [last_name],
    [first_name],
    [category]
)

SELECT
    [document_id],
    [last_name],
    [first_name],
    [category]
FROM
    [StageDB].[dbo].[tbl_documents] D
WHERE
    NOT EXISTS(SELECT [document_id] FROM [ProdDB].[dbo].[tbl_documents] X WHERE D.[document_id] = X.[document_id])

I then have a stored procedure in [StageDB] named proc_ProdDB_update_documents

ALTER PROCEDURE [dbo].[proc_ProdDB_update_documents]
AS

EXEC [ProdDB].[dbo].[proc_update_documents]

User1 has been granted EXECUTE permission on both stored procedures

USE [ProdDB]
GO
GRANT EXEC ON [dbo].[proc_update_documents] TO [User1];
GO

USE [StageDB]
GO
GRANT EXEC ON [dbo].[proc_ProdDB_update_documents] TO [User1];
GO

However, when I execute the stored procedure proc_ProdDB_update_documents from StageDB I get SELECT permission errors although User1 has EXECUTE permissions on the stored procedures.

USE [StageDB]
Go

EXEC [dbo].[proc_ProdDB_update_documents]

Msg 229, Level 14, State 5, Procedure
ProdDB.dbo.proc_update_documents, Line 4 [Batch Start Line 2] The
SELECT permission was denied on the object ‘tbl_documents’, database
‘StageDB’, schema ‘dbo’.

Msg 229, Level 14, State 5, Procedure
ProdDB.dbo.proc_update_documents, Line 23 [Batch Start Line 2] The
SELECT permission was denied on the object ‘tbl_documents’, database
‘StageDB’, schema ‘dbo’.

Completion time: 2024-05-01T15:30:14.6407168+01:00

I do not want the user User1 to have SELECT permissions to the underlying tables, but only EXECUTE permissions to the stored procedures. If I give User1 SELECT permissions on StageDB.tbl_Documents then store procedures work OK, but I am hoping not to do that…