Sql-server – Grant permissions on a stored procedure to tables in another schema

No, you do not want (or need) WITH EXECUTE AS OWNER.

The problem is simply that your two schemas — Schema1 wit the stored procedure, and Schema2 with the tables — have different owners. If you execute the following query, you will see who owns which schemas:

SELECT *, USER_NAME([principal_id]) AS [UserName]
FROM   sys.schemas;

Ownership chaining, the default security mechanism, assumes DML and EXECUTE permissions on objects referenced by the object being accessed, IF the owner is the same. If no owner has been explicitly assigned to an object (default = NULL), then the "owner" is considered to be the owner of the schema in which the object exists.

So, technically your schemas could have the same owner and it's just that the tables have a different owner due to being given one explicitly (via ALTER AUTHORIZATION), but it's much more likely that the schemas have different owners.

Fortunately, this situation is easy to solve using Module Signing.

The concept is to create a certificate in the database containing these schemas and objects, which will then be used to sign the stored procedure that exists in Schema1 but selects from tables in Schema2.

A user is created from the certificate, and is then assigned whatever permission(s) are needed in order for the stored procedure to complete successfully.

No permissions are being granted to anyone that will be executing the stored procedure. The permissions are granted only to the certificate-based user. The connection between the stored procedure and the certificate-based user is (effectively) the public key of the certificate. And that public key is only loaded into the session's security context when a module that has been signed with that certificate is being executed. And that signature requires the certificate's password (in the ADD SIGNATURE statement), and is dropped if anyone changes even a single byte of the stored procedure (or the owner of it). Hence, very secure.

The following illustrates this concept:

Main Setup

CREATE SCHEMA [Schema1];
GO
CREATE SCHEMA [Schema2];
GO

CREATE USER [TestUser] WITHOUT LOGIN;

CREATE TABLE [Schema2].[Stuff]
(
  [StuffID] INT NOT NULL IDENTITY(1, 1)
    CONSTRAINT [PK_Stuff] PRIMARY KEY,
  [Stuff] NVARCHAR(50) NOT NULL
);

INSERT INTO [Schema2].[Stuff] ([Stuff]) VALUES (N'Success!!');

GO
CREATE PROCEDURE [Schema1].[GetStuff]
AS
SET NOCOUNT ON;

SELECT [StuffID], [Stuff]
FROM   [Schema2].[Stuff];
GO

GRANT EXECUTE ON [Schema1].[GetStuff] TO [TestUser];

Test Same Schema Owners

EXECUTE AS USER = N'TestUser';
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

EXEC [Schema1].[GetStuff];
-- Success due to both Schemas having same owner


REVERT;
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

Reproduce Current Situation

SELECT *, USER_NAME([principal_id]) AS [UserName]
FROM   sys.schemas
WHERE  [name] IN (N'Schema1', N'Schema2');
/*
name       schema_id    principal_id    UserName
Schema1    5            1               dbo
Schema2    6            1               dbo
*/


ALTER AUTHORIZATION ON SCHEMA::[Schema2] TO [guest];


SELECT *, USER_NAME([principal_id]) AS [UserName]
FROM   sys.schemas
WHERE  [name] IN (N'Schema1', N'Schema2');
/*
name       schema_id    principal_id    UserName
Schema1    5            1               dbo
Schema2    6            2               guest
*/

Test Current Situation (no extra permissions)

EXECUTE AS USER = N'TestUser';
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

EXEC [Schema1].[GetStuff];
-- ERROR:
/*
Msg 229, Level 14, State 5, Procedure Schema1.GetStuff, Line XXXXX [Batch Start Line YYYYY]
The SELECT permission was denied on the object 'Stuff', database '......', schema 'Schema2'.
*/


REVERT;
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

Module Signing Setup

-- Create Certificate and sign Stored Procedure with it:
CREATE CERTIFICATE [Permission$Schema2]
    ENCRYPTION BY PASSWORD = 'SomePassword'
    WITH SUBJECT = 'Used for selecting from objects in Schema2',
    EXPIRY_DATE = '2099-12-31';


ADD SIGNATURE
    TO [Schema1].[GetStuff]
    BY CERTIFICATE [Permission$Schema2]
    WITH PASSWORD = 'SomePassword';


-- Create Certificate-based User to hold permissions
CREATE USER [Permission$Schema2]
  FROM CERTIFICATE [Permission$Schema2];


-- Grant Certificate-based User ONLY the permissions needed
GRANT SELECT ON SCHEMA::[Schema2] TO [Permission$Schema2];

Test with Module Signing

EXECUTE AS USER = N'TestUser';
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

EXEC [Schema1].[GetStuff];
-- 1    Success!!!


REVERT;
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];