Creating Stored Procedures with user.spName

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

Answer 1

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

Answer 2

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

Answer 3

Sorry, sp_CART_Total was just an example name and not the actual name of the user SP that I'm trying to create.

Answer 4

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!