UDF not there or invisible

Question

UDF not there or invisible

TeraByteMe

SSCarpal Tunnel

Points: 4171
More actions
May 3, 2015 at 8:13 am

#307706

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))

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

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

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.

😎

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 2

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)

TeraByteMe SSCarpal Tunnel Points: 4171 More actions · Answer 3

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))

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 4

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;-)

😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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