do functions in sql server have different permissions rules?
- by jcollum
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?
