mssql Stored Procedure permissions problem

Question

Hello,

I am testing the permissions in mssql and run into a problem. I've made a 'Countries' table and a 'spCountries' Stored procedure. Now I've made a user 'silverlight' and gave it no rights to the Countries table. The user can execute the stored procedure.

Now when I do a Select it fails like it should, but in a exec spCountries, al the data is visible. How can I check for the permissions in the stored procedure?

Is this also possible if the stored procedure does EXEC "SELECT * FROM Countries" instead of just SELECT FROM ...?

Maybe it's also better just to return an empty recordset instead of an error...

Does somebody have an idea?

Answer 1

That is how permissions work in SQL Server.

So you can give permissions on stored procedures without having to give permissions to the underlying objects. This allows you to exercise control over exactly what updates etc. people can make.

Don't give the user silverlight permissions to execute the stored procedure if you don't want them to execute it!

Edit: Although having read the question again it sounds like maybe this is the kind of thing you need?

Answer 2

SELECT has_perms_by_name('dbo.Countries', 'OBJECT', 'SELECT')

Answer 3

That's due to ownership chaining. Basically, if the same principal (e.g. dbo) owns an SP and a table used int it, permissions for the table are not checked.
Actually, this makes sense. For instance, it allows you to give a user acces to some data, but only in specifiс ways coded in the SPs.

If you use dynamic SQL, the permissions are calculated every time. Since SQL2005 you can use EXECUTE AS clause to specify execution context. For example, EXECUTE AS OWNER makes dynamic SQL in the SP execute in SP owner's context, giving similar effect to ownership chaining with static SQL.