Default schema problems - it won't use what I configure!

  • Hi there,

    I am new to database administration, so if something doesn't make any sense here assume the problem is with me and not with you!

    A product we use requires a newer version of SQL than 2000, so my plan was to detach the database from the 2000 box and attach it to a SQL 2008 box. That was fine, but the schema that the database used was, let's say, 'foo' instead of dbo.

    When I connected the application to the database, it looked like all went well, but what really happened was it created the database structure it required under dbo instead of using foo.

    My research led me to believe that if I changed the default schema for the login the application uses, that it might use the existing data instead of forcing us to start over (or forcing me to figure out a way to get all my data from foo into dbo)

    So I did this, however, it just doesn't seem to have worked. I tried using both the UI and using ALTER USER myuser WITH DEFAULT_SCHEMA = foo;

    When I go into the user's profile after disconnecting/reconnecting, it correctly has foo there as the default schema.

    When I connect to the database (in Enterprise Manager) using SQL Server Management Studio and start a new query, I expected a simple select statement, unqualified, to pull from foo instead of dbo, but it's still pulling from dbo. I also tried disconnecting and reconnecting my application using the user with the default foo schema, but it didn't work.

    Until I understand schemas I don't feel like I can contact support for that company. Any advice hugely appreciated!

    Also, is there any way for me to just see what schema my current user is using without knowing what's in the tables and doing selects to tell me?

    Thanks!

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply