title: SELECT only access to SQL Server with access execute any Scalar UDF
body: <p>I'm looking for the best way (best practice) and/or least administrative effort to allow a user read-only (SELECT only) access to a database <strong>and</strong> use any scalar UDFs defined in the database.  I can accomplish the first part of my task by assigning only the db_datareader role to the users Login. However the user cannot see or execute any of the Scalar UDF's in this database (SQL Server 2008 R2).  I know you can run GRANT EXECUTE ON My_UDF TO MY_USER and it will work and it will allow the user access. However, there are two problems with this.  First of all we have hundreds of UDFs and we would need to loop through all of them giving the user EXECUTE to every UDF.  Secondly, whenever a new UDF is created we would need to issue this command again to this READ-ONLY user.</p>
  
  <p>The goal is to give the user as little permission as possible but still allow them to do SELECT's (no UPDATE, INSERT, or DELETE) and be able to use any UDFs in their SELECTS.</p>