>Hi,
>
>In SQL 2000 you can create a function in the master database with a "fn_"
>prefix and then change its owner to "system_function_schema" and it will
>then be available in all databases like an "sp_" stored procedure is.
>
>However, system_function_schema does not exist in SQL 2005's master
>database.  Does anyone know how to make this work in 2005?  I suspect that
>if I can change the schema of the function to "sys" it might work.  However,
>when I try to change the owner to "sys", I get this error message:
>
>You are not the owner specified for the object 'sys' in this statement
>(CREATE, ALTER, TRUNCATE, UPDATE STATISTICS or BULK INSERT).
>
>Has anyone solved this one yet?

"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:t0jo1211ltq49crd8m7n22fle99g65htvr@4ax.com...
> Using system_function_schema is not documented in 2000 and
> has been removed from 2005. Use of the sys schema is
> restricted to internal use by SQL Server itself.
> You need to use a three part name to call the function from
> other databases.
>
> -Sue
>
> On Fri, 17 Mar 2006 12:22:56 -0800, "Daniel Jameson"
> <djame***@childrensoncologygroup.org> wrote:
>
>>Hi,
>>
>>In SQL 2000 you can create a function in the master database with a "fn_"
>>prefix and then change its owner to "system_function_schema" and it will
>>then be available in all databases like an "sp_" stored procedure is.
>>
>>However, system_function_schema does not exist in SQL 2005's master
>>database.  Does anyone know how to make this work in 2005?  I suspect that
>>if I can change the schema of the function to "sys" it might work.
>>However,
>>when I try to change the owner to "sys", I get this error message:
>>
>>You are not the owner specified for the object 'sys' in this statement
>>(CREATE, ALTER, TRUNCATE, UPDATE STATISTICS or BULK INSERT).
>>
>>Has anyone solved this one yet?
>

>Hmmm,
>
>That doesn't get it.  I wrote an example function that returns "select
>max(id) from sysobjects".  When created as a system_function_schema object
>in 2000, I get a different value returned in each database I call it from.
>When created as a normal function in 2005's master database and called from
>other databases using the three part name, I always get the same value,
>master's max object id.  I thus prove that using the three part name does
>not result in the same behavior.
>
>I have to say I'm disappointed with Microsoft for not implementing "fn_"
>functions as a documented feature, given how widely documented this
>undocumented feature is.  Luckily, all our production "fn_" functions are
>simple string and arithmetic functions that don't really have a database
>context, so converting calls to three part names will work for our current
>implementation.
>
>The other solution is to put the CREATE FUNCTION script into a string
>variable and cursor over all the databases and EXEC() the string so the
>function actually exists in all databases, including model (and excluding
>temp).  Just make the operational rule that no database-specific functions
>start with "fn_".

"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:1miu125skq4muclri8t6nh1v4akarjehgc@4ax.com...
> No you haven't proven that three part names does not work. I
> just called functions from other databases using three part
> names and they all work fine.
> If you implement things in production using undocumented
> functionality, well you just got what Microsoft warned you
> about. Not their fault. You were warned by them, you didn't
> heed the warnings.
>
> -Sue
>
> On Mon, 20 Mar 2006 14:37:54 -0800, "Daniel Jameson"
> <djame***@childrensoncologygroup.org> wrote:
>
>>Hmmm,
>>
>>That doesn't get it.  I wrote an example function that returns "select
>>max(id) from sysobjects".  When created as a system_function_schema object
>>in 2000, I get a different value returned in each database I call it from.
>>When created as a normal function in 2005's master database and called
>>from
>>other databases using the three part name, I always get the same value,
>>master's max object id.  I thus prove that using the three part name does
>>not result in the same behavior.
>>
>>I have to say I'm disappointed with Microsoft for not implementing "fn_"
>>functions as a documented feature, given how widely documented this
>>undocumented feature is.  Luckily, all our production "fn_" functions are
>>simple string and arithmetic functions that don't really have a database
>>context, so converting calls to three part names will work for our current
>>implementation.
>>
>>The other solution is to put the CREATE FUNCTION script into a string
>>variable and cursor over all the databases and EXEC() the string so the
>>function actually exists in all databases, including model (and excluding
>>temp).  Just make the operational rule that no database-specific functions
>>start with "fn_".
>