Denied Select Permission to Table in Stored Procedure - SQL Server

Asked Nov 07 '18 at 21:06

Active Mar 12 '19 at 14:35

Viewed 2,036 times

I created a user testUser and assigned it to newly created role executive.

I then created the following procedure:

CREATE PROC dbo.spTest
AS
BEGIN
    SELECT FirstName, LastName 
    FROM dbo.Emp
END

And granted the permission to execute that stored procedure to role:

GRANT EXECUTE ON dbo.spTest TO executive

When I try executing the stored procedure using the testUser, I get the following error message:

The SELECT permission was denied on the object 'Emp', database , schema 'dbo'

Everything I read online says that the GRANT EXECUTE line should work, but it clearly doesn't. I've also read that this error message appears if for dynamic SQL, but I'm pretty sure this is static.

I don't know why it doesn't work. Can anyone help?

edit: I don't want to give SELECT permission to the Emp table (has sensitive data).

edited Mar 12 '19 at 14:35

Tab Alleman

31,483
7
36
52

asked Nov 07 '18 at 21:06

Magalha

1

Make sure the user `executive` has `SELECT` permission on that table... – Aaron Dietz Nov 07 '18 at 21:09
Are you SP and table in the same DB? – Hooman Bahreini Nov 07 '18 at 21:18
1

@AaronDietz: I thought execute permission is enough, even though user may not have read permission to underlying table: [see here](https://dba.stackexchange.com/questions/54926/rights-to-execute-stored-procedure-but-not-read-underlying-tables-directly) – Hooman Bahreini Nov 07 '18 at 21:24
Is the role `executive` actually assigned to `testUser`? – dnoeth Nov 07 '18 at 21:33
@Hooman Hmm I guess `execute` is enough, thanks for the info – Aaron Dietz Nov 07 '18 at 21:35
2

Looks like this can also happen [when ownership chains are wrong](https://dba.stackexchange.com/questions/24555/select-permission-inside-stored-procedure) – Crowcoder Nov 07 '18 at 22:01
@Crowcoder I tried changing the ownership but didn't work – Magalha Nov 08 '18 at 14:17
One thing I forgot to mention, I am using Azure SQL Data Warehouse. Does this matter? – Magalha Nov 08 '18 at 14:33
Can you add a script to your question that fully reproduces the error? (creates the user, role, table, does the assignments, etc...) – Tab Alleman Mar 12 '19 at 14:34

Denied Select Permission to Table in Stored Procedure - SQL Server

0 Answers0