scalar user-defined function require the function to be owner qualified

scalar user-defined function require the function to be owner qualified

Post by Christopher Larma » Sun, 18 Nov 2001 07:24:23



In SQL Server 2000, why does a scalar user-defined function require the
function to be owner qualified?  Does anyone a workaround so not to enter
the owner.
 
 
 

scalar user-defined function require the function to be owner qualified

Post by Narasimhan Jayachandra » Sun, 18 Nov 2001 09:23:02


Yes Possible.

Make sure you have proper permission to create function in the master database.

More over, you should be system administrator or member of sysadmin and serveradmin fixed server roles to execute the sp_configure procedure to create system objects.

Once you create this functions in master database, then you can use this functions in any database of the server.

fn_tables function, will list the Table Name,User Name, Date Created, Type [System/User]
from the database.

DaysOfMonth user definded function is available in Pubs database. if you want to use this function across databases, make it as a built in function, as fn_daysofmonth.

Make sure your built-in function name should be lower cases and start with "fn_" .

------------------------------------------------------------use master
go
exec sp_configure 'allow', 1
reconfigure with override
go
-------------------------------------------------------------- function
drop function system_function_schema.fn_tables
go
create function system_function_schema.fn_tables()
returns table
as
return(
select top 100 percent name as TableName,user_name(uid) as Owner,crdate as DateCreated,
case
when 1 in ( OBJECTPROPERTY(id, N'IsMSShipped'),OBJECTPROPERTY(id, N'IsSystemTable') ) then "System"
else "User" end as Type
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsTable') = 1
order by Type desc,TableName)
go
grant select on system_function_schema.fn_tables to public
go
select * from ::fn_tables()
go

-- no of days in a month - function

The following function named DaysOfMonth is available in Pubs database. if you want to use this function across databases, make it as a built in function, as follows :

-- available in Pubs database
drop function system_function_schema.fn_daysofmonth
go
create  function  system_function_schema.fn_daysofmonth(

)

RETURNS INT
AS
BEGIN

    BEGIN
        --Just return NULL if input string is NULL
        RETURN NULL
    END





END
go

grant exec on system_function_schema.fn_daysofmonth to public
go
select fn_daysofmonth(getdate())
go
------------------------------------------------------------exec sp_configure 'allow', 0
reconfigure with override
go

------------------------------------------------------------execute and see the results
------------------------------------------------------------select * from ::fn_tables()
select fn_daysofmonth(getdate())

-----------------------------------------------

Naras
Narasimhan Jayachandran

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

scalar user-defined function require the function to be owner qualified

Post by Steve Kas » Sun, 18 Nov 2001 09:29:23


Christopher,

  I'm not sure why having to use the two-part function name is a problem, but
if you want a workaround, here's one, made possible by the fact that
table-valued functions don't have to be qualified by the owner.  I expect
your performance will suffer, however!  I'm not really recommending it - just
observing that it can be done...

Instead of

CREATE FUNCTION myF (

) RETURNS varchar(10) AS BEGIN

END
GO

select dbo.myF(5)

you can get the same behavior from this:

CREATE FUNCTION Cheap (

)


return
end

select (select * from Cheap(5))

Steve Kass
Drew University


> In SQL Server 2000, why does a scalar user-defined function require the
> function to be owner qualified?  Does anyone a workaround so not to enter
> the owner.

 
 
 

scalar user-defined function require the function to be owner qualified

Post by Umachandar Jayachandra » Sun, 18 Nov 2001 10:00:53


    You can actually do it using inline table-valued function like:

CREATE FUNCTION Cheap (

)
RETURNS TABLE
AS

    But the problem will be that you cannot do:


    whereas with the normal UDFs, you can do:


    You can only pass constants as parameters to table-valued functions. So
the usability is limited & you can't use the UDF in complex expressions.
    You can of course call a UDF like a SP:


    In this case, you don't need the owner prefix but you can only pass
constants again.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

scalar user-defined function require the function to be owner qualified

Post by Umachandar Jayachandra » Sun, 18 Nov 2001 10:01:39


    This is by design for the non table-valued functions. You can however
call a UDF without owner prefix using EXEC.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

scalar user-defined function require the function to be owner qualified

Post by Erland Sommarsko » Mon, 19 Nov 2001 08:13:15



> In SQL Server 2000, why does a scalar user-defined function require the
> function to be owner qualified?  

Because the sky is blue.

To be more serious, I might have seen an explanation once, but I'm not
sure. I guess it is a pragmatical solution to distinguish built-in
functions like objectproperty() or datalength() from user-defined
functions.

Quote:> Does anyone a workaround so not to enter the owner.

There is none. But what exactly do you want to achieve? While objects can
be owned by many users in the database, I am of the opinion that lift is
much easier if dbo owns the lot.

--
Erland Sommarskog, Abaris AB

SQL Server MVP