How to Set the Default Schema of a Database in SQL Server 2005

How to set the default schema of a database in SQL Server 2005?

A default schema is user-specific:

USE yourDatabase;
ALTER USER [yourUser] WITH DEFAULT_SCHEMA = myschema;

More information about the ALTER TABLE for SQL 2005 might help you as well.

As this is user-specific, if you have multiple users, you will need to execute this query (on each database) for each user whose default schema you want to update.

It is important to note:

The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin
fixed server role. All members of the sysadmin fixed server role have a default
schema of dbo.

In SQL Server 2005 is there a way to set the default schema to anything other than dbo when the user has admin rights?

Sysadmin role members always get defaulted to dbo. It is a designed behaviour and cannot be overruled.

You are best off moving the user out of the Sysadmin role and back into the "normal" userspace

set default schema for a sql query

A quick google pointed me to this page. It explains that from sql server 2005 onwards you can set the default schema of a user with the ALTER USER statement. Unfortunately, that means that you change it permanently, so if you need to switch between schemas, you would need to set it every time you execute a stored procedure or a batch of statements. Alternatively, you could use the technique described here.

If you are using sql server 2000 or older this page explains that users and schemas are then equivalent. If you don't prepend your table name with a schema\user, sql server will first look at the tables owned by the current user and then the ones owned by the dbo to resolve the table name. It seems that for all other tables you must prepend the schema\user.

SQL server schema and default schema

Is the user an SA, if so it will not work, according to the documentation SA users are always defaulted to the dbo schema.

The value of DEFAULT_SCHEMA is ignored
if the user is a member of the
sysadmin fixed server role. All
members of the sysadmin fixed server
role have a default schema of dbo.

Set dbo as default schema for all new tables

You have control over the create table queries? In that case simply specify dbo in create:

CREATE TABLE dbo.dummy ();

For changing default schema for a user see this answer: https://stackoverflow.com/a/8208124/3480246

Migrating from SQL Server 2005 to SQL Server 2008 : forced to use schema name before table name

Default schemea will work for you. What are the issue with this approach?

Change schema name will cause a big issue and not advisable. Where and how much schema name change?(just think).

You just set a default schema with only one procedure first and check, if this is ok. then change the whole database schema.

https://dba.stackexchange.com/questions/21158/net-sql-server-authentication-schema-issue

In sql server 2005, how do I change the "schema" of a table without losing any data?

Change Schema Name Of Table In SQL

Best practice for SQL Server 2008 schema change

Possible to set default schema from connection string?

No, this is done at the database User level, not in the connection string.

For reference, here are all of the properties which can be set in a connection string: https://www.connectionstrings.com/all-sql-server-connection-string-keywords/

Find out default SQL Server schema for session

How about this.

SELECT SCHEMA_NAME()

http://msdn.microsoft.com/en-us/library/ms175068.aspx

SCHEMA_NAME will return the name of the default schema of the caller

Alternatively SCHEMA_ID()

I have changed default schema of a database from dbo to something else but when I wrote query it still took dbo as default schema

When the login is set as 'sysadmin', the default schema will be fixed as dbo and cannot be changed whatsoever.

---

Related Topics