CODEWITHSUNDEEP
sql-serverdatabasestored-proceduressql-grantrole
Victor A Chavez
Victor A Chavez

Reputation: 191

Grant EXECUTE permission Doesn't GRANT Select permission on tables

I have a store procedure that have several selects for checking and balances in our order process, long story short, this store procedure reads (SELECT) about 20 tables with are spread in 3 databases. 

CREATE USER [stageUsrOrder] FOR LOGIN [domain\[my user]]
GO
CREATE ROLE OrderSpecRole AUTHORIZATION [stageUsrOrder]
GO
GRANT EXECUTE on [orders].[ValidateOrderById] TO pmdSchedulerRole
GO

I thought that this statements would give me read access to all the tables inside the store procedure.

I have try 

GRANT EXECUTE on [AVIS].[spReportValidationByLAN] TO pmdSchedulerRole WITH GRANT OPTION 
GO

But it didn't work.

So, How do I grant execute access to the store procedure and SELECT to all the tables inside?

Upvotes: 0

Views: 570

Answers (1)

Ben Thul
Ben Thul

Reputation: 32667

You don't. One of the value propositions of stored procedures is that you can restrict access to certain patterns. For example, if you want people to only search by ID, you give then a stored procedure that takes ID as a parameter.

But if you want to give them arbitrary select access to the underlying tables, you have to do that explicitly.

Upvotes: 1

Related Questions