do functions in sql server have different permissions rules?

Posted by jcollum on Stack Overflow See other posts from Stack Overflow or by jcollum
Published on 2010-05-26T15:19:34Z Indexed on 2010/05/26 15:21 UTC
Read the original article Hit count: 174

Filed under:

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?

© Stack Overflow or respective owner

Related posts about sql-server