Execute Permissions on Stored Procedures

Question

Execute Permissions on Stored Procedures

Andy Watt

SSC Enthusiast

Points: 113
More actions
September 26, 2007 at 4:15 am

#175157

Hi,
I am in the process of tidying and locking down our SQL Servers.
I am only allowing users execute permissions on stored procedures as they only don't need access to the tables or allowed to perform direct queries against the database.
For most of the part it has been successfull, however some of the stored procedures use the EXEC command to perform a query that has been built in the Stored Procedure depending on the paramaters passed through. However, a user with just execute permission for that procedure is not alowed to run that procedure.
I have two examples below, they are not the actual stored prcoedures, just reproducing my problem.
Eg 1 that works - with just execute permissions on the stored procedure, no select access on table.
CREATE PROCEDURE spu_SelectAddress
(
@AddressID INT
)
SELECT AddressID FROM Address WHERE AddressID = @AddressID
Eg 2 doesn't work - with just execute permissions on the stored procedure, no select access on table.
CREATE PROCEDURE spu_SelectAddress
(
@AddressID INT
)
DECLARE @sql VARCHAR(1000)
SET @sql = '
SELECT AddressID FROM Address WHERE AddressID = @AddressID'
EXEC ( @sql )
Any ideas?
Thanks
Andy

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Andy,

the problem is that when you use dynamic SQL (which you shouldn't) you have to grant permissions to the base tables. Exec on the procedure is not enough.

You can find some more info about this here: http://www.sommarskog.se/dynamic_sql.html

Markus

[font="Verdana"]Markus Bohse[/font]

Andy Watt SSC Enthusiast Points: 113 More actions · Answer 2

Thanks for the link Markus

Unfortunatley sometimes Dynamic SQL is required and the only way to go.

The document was interesting and has now pointed me to the following document http://www.sommarskog.se/grantperm.html, which will help me in my cause.

Cheers

Andy