SQL Server setting that changes schema from dbo-CodePudding

Is there an option/setting/default in SQL Server that causes new stored procedures to be placed under your userid instead of schema dbo? I am in the db-owner group, as tested by this script.

For example, I run this in SSMS:

create proc TestName as 
   print 'this is just a test'

The stored procedure created is CORP\myuser.TestName, so CORP\myuser seems to be the schema, right?

Or do I need to specifically specify dbo.TestName when I create it?

Relates to this question: Alter Schema Transfer fails even when I'm a db-owner

CodePudding user response：

The stored procedure created is CORP\myuser.TestName, so CORP\myuser seems to be the schema, right?

Yes, I suspect that is your default schema.

How do I set the schema?

On the object, you say CREATE PROCEDURE dbo.TestName or CREATE PROCEDURE desired_schema_name.TestName. That you haven't had to do this before has been blind luck that nobody writing the code has had a different default schema. But there are other reasons to not be lazy, too: see Bad habits: Avoiding the schema prefix.

On your user, you say:

ALTER USER [CORP\myuser] WITH DEFAULT_SCHEMA = dbo;

But this may have to be run by someone with more privileges, and it may be the case that you don't even have the permissions to create a procedure in the dbo schema (or transfer objects there).