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];
Best Answer
No, you don't need to grant explicit permission on Table1 and Table2, that's one of the objective of embedding code in stored procedure and that's where encapsulation feature comes into effect.
Please check below link from Microsoft:
Managing Permissions with Stored Procedures in SQL Server
Use the code below to grant execute permission:
Hope above helps.