system udf

• markwnorsoft

  SSC Eights!

  Points: 827

  More actions

  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

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  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.

• David A. Long

  SSCrazy Eights

  Points: 8421

  More actions

  June 23, 2005 at 12:17 am

  #567758

  Check out the script for an example of how:

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

  Andy

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  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]