Preventing stored procedures from writing to databases

I am a database developer mostly writing SSRS reports. I would like to create stored procedures and assure the DBAs that those stored procedures cannot write to the production databases, without the DBAs having to carefully review the stored procedures.

I create the stored procedures in DatabaseA. I need the stored procedures to SELECT data in DatabaseB.

I want any user to be able to safely EXECUTE a stored procedure in DatabaseA but not be able to use that stored procedure to INSERT/UPDATE/DELETE data in DatabaseB, regardless of any other permissions which the user may have.

Can the DBAs set permissions at the database or procedure level to enforce this?

This is for SQL Sever 2016.

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 itself






• EXEC 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

StackExchange Database Administrators Q#305252, answer score: 7