In SQL Server 2005 is there a way to set the defau

Question

This question already has an answer here:

• SQL server schema and default schema 3 answers

I am using SQL Server 2005 with Windows authentication. My login has administrator rights. For my login I have a user mapped to a database with the default schema set to "my_schema". My login is not the owner of the database. When I log on though, and try to execute a simple select statement on a table in "my_schema" without specifying the schema name, I get an invalid object error. This does not happen when I log on as a user with no admin privileges. What I think is happening is that SQL Server is changing the default schema to "dbo" because I have admin rights. Is there a way to work around this?

Answer 1

You can set the default schema with the "DEFAULT_SCHEMA" option using either the CREATE USER or ALTER USER commands

CREATE USER exampleUser WITH DEFAULT_SCHEMA = exampleDB;
ALTER USER exampleUser WITH DEFAULT_SCHEMA = exampleDB;

Answer 2

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