Post reply

system udf

  • June 22, 2005 at 11:23 am

    #88751

    Hi,

    I'm running SQL server 2000 sp3 on win2003 with nt authentication.

    What I wanted to do is create a system udf that everybody can use.

    Here is the function.

    EXEC SP_CONFIGURE 'ALLOW UPDATES', 1

    RECONFIGURE WITH OVERRIDE

    USE MASTER

    GO

    CREATE FUNCTION

    system_function_schema.fn_nstgetuseronly()

    RETURNS VARCHAR(50)

    BEGIN

      RETURN(select substring(system_user,charindex('\',system_user)+ 1,50))

    END

    GO

    EXEC SP_CONFIGURE 'ALLOW UPDATES', 0

    RECONFIGURE WITH OVERRIDE

    It works if you are an administrator or if I add a server role of system

    administrator to the user or group.

    Is there a way to add permissions to the system udf?

    When I try to

    GRANT EXEC ON [system_function_schema].[fn_nstgetuseronly] TO READWRITE

    I get this error.

    Server: Msg 208, Level 16, State 11, Line 1

    Invalid object name 'system_function_schema.fn_nstgetuseronly'.

    Any idea?

    Thanks

    Mark

  • June 22, 2005 at 11:34 am

    #567588

    Why not just put that in a shared database that all apps can use, or simply in all needed databases... you wouldn't need that much permission to access it.

  • June 23, 2005 at 12:17 am

    #567758

    Check out the script for an example of how:

    http://qa.sqlservercentral.com/scripts/contributions/1522.asp

    Andy

  • June 23, 2005 at 12:38 am

    #567761

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01l1.asp

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

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

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