Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have granted a user execute permission for a stored procedure through SSMS, but when he tries to execute it, he gets the error:

The SELECT permission was denied on the object '[table name]', database '[database name]', schema 'dbo'.

Does the user need to be granted permission for any tables that the stored procedure uses? That wouldn't really make any sense to me...

share|improve this question
    
Are you using the correct schema prefix (a) on the stored procedure, (b) in the select, and (c) when calling the stored procedure? Is it possible that someone has explicitly revoked/denied select permission on the table? Have you tried with EXECUTE AS OWNER? –  Aaron Bertrand Sep 19 '12 at 18:23
    
Also, what version of SQL Server are we talking about here? 2000? 2012? Something in between? –  Aaron Bertrand Sep 19 '12 at 18:31
    
@AaronBertrand 2008 (not R2). It's actually a view. The schema prefix is set everywhere I know how. I don't see Execute as Owner in the permissions settings –  roviuser Sep 19 '12 at 18:55
2  
Are you using dynamic SQL within the stored procedure? –  Mr.Brownstone Sep 19 '12 at 19:11
1  
@Mr.Brownstone I work with OP--that is the issue (confirmed). –  Michael Haren Sep 20 '12 at 20:11
show 3 more comments

2 Answers

up vote 1 down vote accepted

Ok, on the basis of the above comment and as per my suspicion - it seems as though you are trying to execute dynamic SQL within your stored procedure. What you need to remember is that when you do this it does not get executed within the context of the stored procedure - it gets executed within a new session. Because of this, the fact that the statement is being called within a stored procedure is a mute point and you will need to grant explicit permission on the objects that your dynamic SQL is using. If you don't want to do this I would refactor your stored procedure to not use dynamic SQL.

The below link from Microsoft should help you with your problem:

http://support.microsoft.com/kb/301299

I hope this helps you.

share|improve this answer
add comment

It sounds like there are different owners of the procedure as well as the underlying object that the SELECT is querying. This all has to do with Ownership Chains. See the below example for a brief explanation and demonstration to what I'm talking about:

use YourTestDatabase;
go

create login TestLogin1
with 
    password = 'password',
    check_policy = off;
go

create user TestUser1
for login TestLogin1;
go

create table Table1
(
    id int identity(1, 1) not null,
    SomeString varchar(30) not null
        default replicate('a', 30)
);
go

insert into Table1
values(default);
go 10

create proc Proc1
as
    select *
    from Table1;
go


grant execute
on Proc1
to TestUser1;
go

-- this works because permissions aren't checked
--  on Table1.  That is why TestUser1 can get the
--  result set without SELECT permissions on Table1
execute as user = 'TestUser1';
go

exec Proc1;
go

revert;
go

-- let's change the owner of Proc1 so that the 
--  ownership chain is broken and permissions are
--  checked on Table1
alter authorization
on Proc1
to TestUser1;
go

-- this no longer works because permissions are now
--  checked on Table1, which TestUser1 does not have
--  SELECT permissions on
execute as user = 'TestUser1';
go

exec Proc1;
go

revert;
go

If you want to find out the ownership of your objects, you can run the below query (obviously with changing the WHERE clause to include your specific object names):

select
    o.name,
    o.type_desc,
    case
        when o.principal_id is null
            then sp.name
        else dp.name
    end as principal_name
from sys.objects o
inner join sys.schemas s
on o.schema_id = s.schema_id
left join sys.database_principals dp
on o.principal_id = dp.principal_id
left join sys.database_principals sp
on s.principal_id = sp.principal_id
where o.name in
(
    'Table1',
    'Proc1'
);
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.