2

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?

YWE
  • 2,849
  • 28
  • 42

2 Answers2

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

sql_williamd
  • 441
  • 2
  • 4
  • A more detailed answer with link to the official documentation can be found here: http://stackoverflow.com/a/3806301/806549 –  Jun 05 '13 at 11:08
-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;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
b8b8j
  • 584
  • 1
  • 5
  • 13
  • I already set the default schema like this. But it does not work when I have admin rights. – YWE Jul 20 '10 at 17:44
  • 1
    Sorry about that, I don't believe you can change the default schema as a member of "sysadmin" group. It's generally considered bad practice to use a sysadmin as a general user, and creating an alternate general user circumvents your problem. – b8b8j Jul 20 '10 at 17:57
  • If you post code or XML, **please** highlight those lines in the text editor and click on the "code" button (101 010) on the editor toolbar to nicely format and syntax highlight it! – marc_s Jul 20 '10 at 19:17