views:

21

answers:

1

Let's say I have a function that is accessed in many stored procedures. If that function has no entries in sys.database_permissions does any account that can run one of the stored procs automatically run the function?

This is the query I'm using:

 exec sp_depends 'dbo.theFunction' 

 -- for each object in those results, run this query

 select *  
 from sys.database_permissions dp
 where 
 grantee_principal_id=USER_ID('theAccount')
 and 
 major_id=object_id('dbo.theFunction')
 and minor_id=0
 and ( state_desc = 'GRANT' or state_desc = 'SELECT' ) 
+1  A: 

There is never an implicit GRANT, on anything. How the 'table accessed from stored procedure' or 'function accessed from stored procedure' works usually is through ownership chaining:

When multiple database objects access each other sequentially, the sequence is known as a chain. [...] When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.

Remus Rusanu
I knew it had to be something like this. Thanks for finding it, my coworkers and I were stumped about why we were seeing this behavior.
jcollum