locked
Creating Stored Procedures with user.spName RRS feed

  • Question

  • I'm having a terrible time trying to create stored procedures that are owned by a user (either the one I'm currently logged in with or logged in as SA and specifying the name).  For example:

    USE dbName
    GO
    CREATE PROCEDURE [dbUser].sp_CART_Total
    (
       @Param1 varchar(50),
       @TaxParam money
    )
    AS
    ......

    When I do this, I get an error message that says:
    MSG2760, Level 16, State 1, Procedure sp_CART_Total, Line 3
    The specified schema name "dbUser" either does not exist or you do not have permission to use it.

    This even happens when I'm logged in as the friggin' SA account.  What is going on?

    Oh, I'm completely new to SQL2008. I've been using SQL2000 since 2001.

    What am I missing?

    Thanks,
    Owen
    Wednesday, February 4, 2009 11:51 PM

Answers

  • Try this,

    First create dbuser schema

    USE [DatabaseName]
    GO
    CREATE SCHEMA [dbuser] AUTHORIZATION [dbo]

    and then execute your procedures as

    USE dbName
    GO
    CREATE PROCEDURE [dbUser].sp_CART_Total
    (
       @Param1 varchar(50),
       @TaxParam money
    )
    AS
    ......

    Rajesh Jonnalagadda http://www.ggktech.com
    Thursday, February 5, 2009 6:57 AM

All replies

  • In SQL Server 2005 schemas are fully implemented as a name space and provide another level of security and the SA account is a member of the dbo schema. 

    In SQL 2005 rather than just implementing the prefix of an object with the owner or creator schemas are used, which act like a group for principals in SQL 2005.  Database users can belong to a schema, and permissions can then be granted on the schema, again acting very much like a group.

    The below MSDN link provides documentation on using schemas.  I would also HIGHLY recommend not prefixing user defined stored procedures with sp_.  This naming convention is used to denote system stored procedures and can cause confusion.

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

     

    Hope this helps


    David Dye
    Thursday, February 5, 2009 12:42 AM
  • Try this,

    First create dbuser schema

    USE [DatabaseName]
    GO
    CREATE SCHEMA [dbuser] AUTHORIZATION [dbo]

    and then execute your procedures as

    USE dbName
    GO
    CREATE PROCEDURE [dbUser].sp_CART_Total
    (
       @Param1 varchar(50),
       @TaxParam money
    )
    AS
    ......

    Rajesh Jonnalagadda http://www.ggktech.com
    Thursday, February 5, 2009 6:57 AM
  • Sorry, sp_CART_Total was just an example name and not the actual name of the user SP that I'm trying to create.
    Thursday, February 5, 2009 2:10 PM
  • Still an issue?

    Thanks!


    Ed Price (a.k.a User Ed), SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, June 6, 2013 4:12 PM