Post reply

Change Prefix On Stored Procedure

  • October 31, 2006 at 8:16 am

    #174996

    I have all these wonderful SP's created, I was so excited, and then I realized that each of them is 'prefixed'? with my user name and not DBO, how do I fix.

    The following is the code that I used to create the code:

    Create Procedure QCP_Create_QICC_Table

    As

    Code followed

    How do I fix and keep from happening again?

  • October 31, 2006 at 8:50 am

    #668995

    Your create statement should look like this:

    create procedure dbo.ProcName

  • October 31, 2006 at 9:41 am

    #669013

    The "prefix" is the owner of the object.  If you're not sysadmin of database owner, it defaults to your user name unless you specify another.

    To fix, run this for each stored procedure:

    sp_changeobjectowner 'myuser.procname', 'dbo'

    Greg

    Greg

  • October 31, 2006 at 9:44 am

    #669015

    Easiest way to change the owner.  I couldn't remember the syntax for the sp_chengeobjectowner sproc even though I have used it on several occasions (that's what BOL is for though).

  • October 31, 2006 at 1:26 pm

    #669063

    I thought about that, when I tried it initially, I put the DB name first and it errored out, thank you for your advice, it worked perfectly.

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

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