do functions in sql server have different permissions rules?

Question

Here's the situation. I'm writing an automated test that walks the list of dependencies for a proc and determines if an acct has rights for all of the dependent objects.

My code looks like this:

exec sp_depends 'the_proc_name'

-- run this query on the results of sp_depends: 

select
case                          
when exists (                                   
select *
from sys.database_permissions dp
where grantee_principal_id=USER_ID('TheAccount')                                     
    and major_id=object_id('dbo.theDependentObject')                                     
    and minor_id=0
    and state_desc = 'GRANT')
then 'true'
else 'false'                                
end; 

It all seems to be working fine, but there's a hiccup when it encounters a function. I have one case where TheAccount doesn't have rights to a function (the query above returns false). However the proc that calls the function in question runs fine when running under TheAccount. So there's either something wrong with my test code or functions have special permission behavior in SQL-Server that I'm not aware of.

Should I change the code to only search for 'DENY' instead of 'GRANT'? Do functions that are called in procs inherit the permissions of the calling proc except when the execute rights are explicitly denied? Does my code suck?

Answer 1

When you are running static SQL from a stored proc, the stored proc runs with the authority of the id that last created/modified the stored proc; not the id of the person running the stored proc.

For example, this is the same ability that allows you to use a stored proc to run an Insert statement without giving the person running the stored proc Insert Authority on the underlying table.

One note - this does not apply when you are using dynamic SQL (with the exec statement). In that case, the person running the stored proc, must have the necessary authority for that SQL statement.

So I'm not sure if your unit tests will provide you what you are looking for since the rights to dependent objects are taken care of, to some extent, by the way SQL Server handles Stored Proc security.