function owner

• Tim Ellison

  SSChasing Mays

  Points: 639

  More actions

  November 25, 2003 at 9:30 am

  #160982

  I'm trying to use a UDF in Query Analyser which I created earlier but get a permissions error.

  For example

  select udf_format_date(getdate())

  and I get

  Server: Msg 195, Level 15, State 10, Line 1

  'udf_format_date' is not a recognized function name.

  When I run

  select dbo.udf_format_date(getdate())

  This works!!

  Problem is I am logged into QA as the dbo. Why do I need to specify dbo as the object owner ? What am I missing here ?

  Thanks in advance

• DavidT

  SSCommitted

  Points: 1727

  More actions

  November 25, 2003 at 9:38 am

  #483570

  Books on line: User Defined Functions:

  Calling User-Defined Functions

  When calling a scalar user-defined function, you must supply at least a two-part name:

  SELECT *, MyUser.MyScalarFunction()

  FROM MyTable

  Table-valued functions can be called by using a one-part name:

  SELECT *

  FROM MyTableFunction()

  Just the way it is!!

• Sopheap Suy

  SSCertifiable

  Points: 7893

  More actions

  November 25, 2003 at 9:41 am

  #483572

  This is the possibility:

  you are not the dbo of the database. You might just has db_owner role. You created the function under domain\username.udf_format_date

  as well as create another function dbo.udf_format_date and sql server doesn't know which one you wanted to call.

  mom

• Tim Ellison

  SSChasing Mays

  Points: 639

  More actions

  November 25, 2003 at 10:09 am

  #483577

  mom - I did create the function using domain\username. This username belongs to the 'system administrators' server role so I don't think permissions is the problem

  DavidT - I think you have identified the problem. Thanks for the advice.