CODEWITHSUNDEEP
sql-serverstored-procedures
Zee
Zee

Reputation: 642

Executing a stored procedure in SQL Server

I have a stored procedure called testSP in my SQL Server Express database.

I am able to execute it using

exec [db_owner].[testSP]

but if I use exec testSP it doesn't work.

What is the cause of this?

I have other databases which do not exhibit this behavior.

Thanks for your help.

Upvotes: 0

Views: 150

Answers (1)

Nick.Mc
Nick.Mc

Reputation: 19184

Your user is set up with dbo as it's default schema. That's pretty normal

when you run 

exec testSP

it's using your default schema which is dbo, so it is running this:

exec [dbo].[testSP]

which doesn't exist.

When you run 

exec [db_owner].[testSP]

it find and runs that stored procedure

I don't know the background but I guess someone has incorrectly/accidentally created and used a schema called db_owner

In all the db's that work, I guess the objects are in the dbo schema or your user is set up to use the correct schema. Go look in the object browser and compare

If you want to move the stored procedure into the dbo schema run this:

ALTER SCHEMA dbo TRANSFER [db_owner].[testSP];

If you want to change your users default schema to db_owner run this:

ALTER USER [youruser] WITH DEFAULT_SCHEMA = db_owner;

I reckon the db_owner schema is an accident though.

Upvotes: 1

Related Questions