MSSQL 2000 / MSSQL 2005 Schema Issue.

[aba]

When you are using any user other than sa in MS SQL 2000, the database
tables get associated with the schema of that user instead of dbo.

for example in my case i was having the username as aft and the tables
that are getting created were created as aft.user_ instead of
dbo.user_......

When you take a backup and restore the database in MS SQL 2000, the
restoration will get finished successfully.... but when try to login
as aft and try accessing the database you won't be able to do so.
This is because the relationship between the user and schema breaks
during restoration due to change in SID (don't ask me what an SID is,
i don't know, it would be nice if anybody else could throw some light
on it)...

To solve that we can use this query
-------------------------------------------------------------
use [database name]
sp_change_users_login AUTO_FIX, [login]
-------------------------------------------------------------

Now if you had migrated the database from MS SQL 2k to MS SQL2k5, and
have proceeded without resetting the schema to dbo. Everything goes
fine. till you backup and restore now.... This time again a similar
situation with a slight difference occurs. Now you will able to login
and work on the database but you can run queries like
---------------------------------------
select * from [table name]
---------------------------------------
this will return a error invalid object name [table name]

but you will be able to execute the same query like this.
----------------------------------------------------
select * from [schema].[table name]
----------------------------------------------------

The problem occurs because, the relationship between the user and the
login breaks. we will have to re-establish this relationship. We can
do it by running this query........
----------------------------------------------------------------------
use database
alter user [user name] with login = [login name]
----------------------------------------------------------------------
This does the trick of reestablishing the relationship between the
login and the user....


Correct me if i wrong...
In MS SQL 2k user and login are same
In MS SQL 2k5 user and login are different

[aba]

Just in case you need another user for the same database and you want
things to happen properly, you will have to assign the schema
permissions to this user too.

you can achieve this with this query
----------------------------------------------------------
use [database]
alter user [user_name] with default_schema = [schema name]
-----------------------------------------------------------

Note:

IN MSSQL 2K5, all these alter user commands are specific to currently
used database and not across the whole database server. So you will
have to set these to each database separately. i don't have an idea
about MSSQL 2K