Post reply

function owner

  • 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

  • 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!!

  • 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

  • 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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply