stored procedure permission

[youngun]

I have a table dbo.EMPLOYEE and I have a stored procedure dbo.CHECKLOGIN which checks the empl_id and password.

The stored procedure is simple:

select * from EMPLOYEE where empl_id = @username and password = @password

I currently login as "ABC" which has the permission to execute the stored procedure CHECKLOGIN, but why I got error saying I don't have the permission to select from EMPLOYEE because it belongs to dbo?

Isn't it true that I can do whatever thru stored procedure as long as I have the execute permission?

Thanks!

 

[fluteplr]

As long as the user who created the stored procedure has permission to resource.

In other words if user ABC creates the stored procedure but does not have permissions to the employee table the stored procedure will fail.

 

[youngun]

The user who created the stored proceudre has the sysadmin privilege, doesn't he automatically have the permission to everything?

 

[fluteplr]

he should. I would try loging in as the DBO and creating the procedure.

 

[youngun]

The sa created the procedure and now I login as myself who doesn't have the permission to the table EMPLOYEE. But the sa gives me permission to execute procedure CHECKLOGIN. I got error due to lack of permissions. I don't understand why this happens because sa is the dbo and owner of everything, and I don't see why I can't access EMPLOYEE thru stored procedure which I have permission to execute!

 

[fluteplr]

Sorry you've got me. It should work as you describe. Maybe someone else has seen this before.

 

[youngun]

Can anybody solve this problem for me?