UDF not there or invisible

  • I started a new position recently and my Manager says there is a UDF out on a database but I can't find it anywhere not under object explorer in SSMS nor when querying the sys.functions view.

    I have been working around sql server quite a while but I have never run across this kind of thing before. I have read/write permissions on the database. Is it possible that the functions is really there but I don't have permissions to see it?

    Thanks

    A clever person solves a problem. A wise person avoids it. ~ Einstein
    select cast (0x5365616E204465596F756E67 as varchar(128))

  • TeraByteMe (5/3/2015)


    I started a new position recently and my Manager says there is a UDF out on a database but I can't find it anywhere not under object explorer in SSMS nor when querying the sys.functions view.

    I have been working around sql server quite a while but I have never run across this kind of thing before. I have read/write permissions on the database. Is it possible that the functions is really there but I don't have permissions to see it?

    Thanks

    Quick suggestion, search for it in sys.all_SQL_modules, if you cannot find it there then add a permission to view definitions.

    😎

  • I agree with Eirikur. Read/Write privs don't give you privs to view definitions of objects. You need those privs to see functions. Of course, you need those privs to see any object. If you can see other objects, it might be because the PUBLIC or whatever AD group you belong to may have been given privs on specific objects or a specific schema but people aren't normally so detail oriented on privs.

    If you can see other objects, it may be that the function doesn't actually exist in the given database. There could be a synonym. Search for the name of the function in the base_object_name of sys.synonyms.

    Of course, it may also be that someone is mistaken and that it's not a function but a stored procedure, view, or even computed column.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I didn't say anything about viewing the definition of the object I said I can't see it anywhere. I know it must be a function cause I can see it being used in the select statement of SSIS package. Tomorriw morning I will give some of your suggestions bs a try. Appreciate the responses.

    A clever person solves a problem. A wise person avoids it. ~ Einstein
    select cast (0x5365616E204465596F756E67 as varchar(128))

  • I didn't say anything about viewing the definition of the object I said I can't see it anywhere.

    Think you answered your question here;-)

    😎

  • TeraByteMe (5/3/2015)


    I didn't say anything about viewing the definition of the object I said I can't see it anywhere.

    The way SQL permissions work is that you cannot see an object if you have no permissions on it. So if you do not have either EXECUTE or VIEW DEFINITION on the function or its schema, you won't be able to see the object.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

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