I recently found out that some schemas in my SQL2005 box are not of the default dbo (this is apart from some pre-determined schemas that we have). This seems to happen when I run sp_adduser to add a login as a user of a particular database.
CREATE LOGIN [DOMAIN\mylogin] FROM WINDOWS
GO
USE MyTestDB
GO
sp_adduser ‘DOMAIN\mylogin’
This will create the user DOMAIN\mylogin with DefSchemaname DOMAIN\mylogin.
To change the default schema back to dbo:
USE MyTestDB
GO
ALTER USER [DOMAIN\mylogin] WITH DEFAULT_SCHEMA = dbo
Note: This problem doesn’t exist if you use CREATE USER. So try to use that next time!
Note 2: This issue doesn’t apply to groups. Sp_adduser will not allow you to add a group, only individual users. Should you run CREATE USER [group] without specifying anything else, the default schema will be NULL.
