Ownership chaining says "no."
The way ownership chaining works in SQL Server, granting EXEC on a stored procedure implicitly grants permission to all dependent objects, so that the stored procedure can do whatever it wants within that database.
The traditional answer to this is that you need to trust the process that creates and changes the stored procedure. If you don't trust that process, then you can't trust granting execute to a low-privileged user.
Quick demo
Take a look at how ownership chaining affects things. In this example, the ReadOnlyUser is explicitly denied INSERT on the table, and explicitly granted EXEC` on a stored procedure (which just does an insert into that table).
You'll see that the user is denied the ability to insert directly, but is able to run the stored procedure, which successfully inserts.
USE [TestingPermissions]
CREATE TABLE dbo.ReadOnlyPlease (
SomeColumn varchar(100)
);
GO
INSERT INTO dbo.ReadOnlyPlease (SomeColumn)
VALUES ('One'),('Two'),('Three');
GO
CREATE PROCEDURE dbo.CanItWrite
AS
SET NOCOUNT ON;
INSERT INTO dbo.ReadOnlyPlease (SomeColumn)
SELECT CONVERT(varchar(100),GETDATE(),121);
GO
CREATE LOGIN ReadOnlyUser WITH PASSWORD = 'TheSecure1!';
CREATE USER ReadOnlyUser FROM LOGIN [ReadOnlyUser];
DENY UPDATE ON dbo.ReadOnlyPlease TO [ReadOnlyUser];
GRANT EXECUTE ON dbo.CanItWrite TO [ReadOnlyUser];
GO
EXECUTE AS LOGIN = 'ReadOnlyUser';
--This will fail
INSERT INTO dbo.ReadOnlyPlease (SomeColumn)
SELECT CONVERT(varchar(100),GETDATE(),121);
--this will succeed!
EXEC dbo.CanItWrite;
REVERT;
SELECT * FROM dbo.ReadOnlyPlease;
But wait, there's more!
I said earlier:
the stored procedure can do whatever it wants within that database.
Within the database.
Cross-database ownership chaining is a whole separate thing. It is disabled by default. That means you can put your stored procedure into a different database, and get around the ownership chaining implicit permissions.
If you create a separate database for just your reporting stored procedures, you'll need to manage every permission for every object that is accessed by the procedure (either granularly or via high level grant/role membership like db_datareader).
Demo time
Here, I create a separate database (ReportingProcedures), create a synonym to point across at the table in the other database, and create the same exact stored procedure. I could have edited the procedure to use 3-part names, but I like synonyms, so I used them.
Permissions-wise:
- There is a user for both databases (
TestingPermissions and ReportingProcedures)
INSERT is explicitly denied on the table itselfEXEC is granted on the identical procedures in the two databases
You'll see that the procedure in TestingPermissions succeeds because the data & code are in the same database (thanks ownership chaining!), but the procedure in ReportingProcedures fails because there is no ownership chaining!
CREATE DATABASE ReportingProcedures;
GO
USE ReportingProcedures
CREATE SYNONYM dbo.ReadOnlyPlease FOR TestingPermissions.dbo.ReadOnlyPlease;
GO
CREATE PROCEDURE dbo.CanItWrite
AS
SET NOCOUNT ON;
INSERT INTO dbo.ReadOnlyPlease (SomeColumn)
SELECT CONVERT(varchar(100),GETDATE(),121);
GO
CREATE USER ReadOnlyUser FROM LOGIN [ReadOnlyUser];
GRANT EXECUTE ON dbo.CanItWrite TO [ReadOnlyUser];
GO
EXECUTE AS LOGIN = 'ReadOnlyUser';
--This is the one that worked; still works
EXEC TestingPermissions.dbo.CanItWrite;
--This one fails though
EXEC ReportingProcedures.dbo.CanItWrite;
Different strokes for different folks
In the way that I used two databases as "containers" to separate the code from the data, you could also use different schemas with different owners. As long as the two schemas are owned by different users, then you can also successfully "break" the permission inheritance of ownership chaining
