none
Calling a user defined function without Schema Prefix RRS feed

  • Question

  • Calling a user defined function without Schema

    Prefix


     Hi ,

    I created a LOGIN User U1 and CREATED a database user DU1 and linked him with the Login User and I created a schema say S1 and set S1 as the default schema for DU1 .

    I have a stored Procedure  and a userdefined function inside S1 . I logged in as the Login user, I can execute that procedure directly  without any schema prefix  but I cannot call a user defined function F1 directly (without schema prefix) . it throws an exception

    "function name is not a recognized built-in function name"
    
    .


    How can i call a UDF directly with out a schema prefix

    THE STEPS I FOLLOWED ARE :
    -----------------------------

    --Login User Creation
    CREATE LOGIN S11    WITH PASSWORD = 'Passw@rd!';
    --Database User Creation and assign to default schema
    CREATE USER S11 FOR LOGIN S11    WITH DEFAULT_SCHEMA = S2;
    --Giving User Privilege
    EXEC sp_addrolemember db_owner,'S11'

    --Calling Procedure
    Execute as user = 'S11'
    exec MYSTOREDPROCEDURE1 -- Working fine
    USERDEFINEDFUNCTION() -- Not working throws error  

    HOW CAN I CALL A UDF WITHOUT SCHEMA PREFIX
    Monday, September 20, 2010 2:45 PM

Answers

  • Hi, 

    As I know for calling a UDF, you have call them with Schema Prefix. 

    http://msdn.microsoft.com/en-us/library/ms186755.aspx


    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    • Proposed as answer by Naomi NModerator Sunday, September 26, 2010 6:53 PM
    • Marked as answer by Ai-hua Qiu Wednesday, September 29, 2010 8:45 AM
    Monday, September 20, 2010 2:53 PM
  • As I know for calling a UDF, you have call them with Schema Prefix. 

    Correct. This is just one of those things that are by design...
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by Ai-hua Qiu Wednesday, September 29, 2010 8:45 AM
    Monday, September 20, 2010 6:05 PM
    Moderator

All replies

  • Hi, 

    As I know for calling a UDF, you have call them with Schema Prefix. 

    http://msdn.microsoft.com/en-us/library/ms186755.aspx


    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    • Proposed as answer by Naomi NModerator Sunday, September 26, 2010 6:53 PM
    • Marked as answer by Ai-hua Qiu Wednesday, September 29, 2010 8:45 AM
    Monday, September 20, 2010 2:53 PM
  • Hello,

    What Error you are getting? 

    What syntax you are using to call a function?

     

    Thanks

    Monday, September 20, 2010 3:05 PM
  • without schema name sql server considering user definded function is the system build in function so when it has tring to search in the system definided function section it has not able to get the name so that it has throwing the error message.

     

    Monday, September 20, 2010 3:17 PM
  • As I know for calling a UDF, you have call them with Schema Prefix. 

    Correct. This is just one of those things that are by design...
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by Ai-hua Qiu Wednesday, September 29, 2010 8:45 AM
    Monday, September 20, 2010 6:05 PM
    Moderator