Executing a stored procedure in SQL Server

Question

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.

Answer 1

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.