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?
views:
22answers:
2
A:
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;
b8b8j
2010-07-20 17:35:24
I already set the default schema like this. But it does not work when I have admin rights.
YWE
2010-07-20 17:44:28
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
2010-07-20 17:57:41
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
2010-07-20 19:17:43
+1
A:
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
WilliamD
2010-07-20 18:59:52