0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
NealWalters
  • 17,197
  • 42
  • 141
  • 251
  • 2
    As per the documentation "The name of the schema to which the procedure belongs. Procedures are schema-bound. If a schema name is not specified when the procedure is created, the default schema of the user who is creating the procedure is automatically assigned." Its best practice to always specify the schema for anything you do in SQL Server. – Dale K Apr 26 '22 at 20:24
  • Ok, thanks. How do I set the schema? I just don't remember every having to do this before. This db might have some different setting. – NealWalters Apr 26 '22 at 20:25
  • I'm trying to debug the "AutoEdit" script, which is 7000+ lines of code that I didn't write. – NealWalters Apr 26 '22 at 20:27

1 Answers1

2

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).

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thanks, I think it was the alter user command I was looking for. As I said, I'm trying to fix a bug in the AutoEdit script, which apparently assumes you are in the dbo schema. What's the command to show the default_schema for a user - I'll google that next... – NealWalters Apr 26 '22 at 20:43
  • SELECT SCHEMA_NAME() – NealWalters Apr 26 '22 at 20:46
  • Also, I did state that I am in the group db_owner. Every company I have ever worked apparently defaulted to 'dbo', and at this one, it defaults to "domain\userid". Thanks again! I have edited more to my AutoAudit answer here: https://stackoverflow.com/questions/72019509/alter-schema-transfer-fails-even-when-im-a-db-owner/72019914#72019914 – NealWalters Apr 26 '22 at 21:09
  • 1
    @Neal The role you’re in doesn’t matter; the default schema for your user is independent. `SCHEMA_NAME()` will get it for the current user, but for any other user: `SELECT default_schema_name FROM sys.database_principals WHERE name = N'CORP\MyUser';` – Aaron Bertrand Apr 26 '22 at 21:14