User & Schema Issues on SQL Server 2005

Basically, this is due to the new schema / user separation in SQL Server 2005.  By default any users created in SQL Server 2005 will have a default schema of dbo, however for those users that were already part of the restored database, SQL Server 2005 has created a default schema of the same name as the user.  This causes a problem when attempting to delete the users as the drop user procedure (mysp_dropuser) will not drop the schema and a user cannot be dropped if it owns a schema.

As a solution to this I would like modify the default schemas of all OurProduct® users to dbo.  I would then like to drop those schemas that were automatically created for the existing OurProduct® users.  This will ensure that OurProduct® users do not own schemas.  Therefore, preventing any errors on attempting to delete users in OurProduct®.

To change the default schema of a user you need to run the following SQL:

ALTER USER [user] WITH DEFAULT_SCHEMA = [new schema]

e.g.

ALTER USER myUser WITH DEFAULT_SCHEMA = dbo

In order to create the SQL required to do this for all users could you run the following SQL and output the result to text.  You can then run the SQL that is output:

SELECT 'ALTER USER '+ user_id + ' WITH DEFAULT_SCHEMA = dbo'
FROM staff s
JOIN sys.sysusers su
ON su.name = s.user_id
WHERE s.is_Deleted = 'N'

The next part is then to drop those redundant schemas.  The SQL required for this would be as follows:

DROP SCHEMA [schema]

e.g.

DROP SCHEMA myUser

Again, in order to create the SQL required to do this for all users could you run the following SQL and output the result to text.  You can then run the SQL that is output:

SELECT 'DROP SCHEMA '+ user_id
FROM staff s
JOIN sys.schemas su
ON su.name = s.user_id
WHERE s.is_Deleted = 'N'

One further step that may be required is to re-associate any existing users with the new logins.  To do this, you just need to run the following SQL:

EXEC sp_change_users_login 'Auto_Fix', '[user]'

e.g.

EXEC sp_change_users_login 'Auto_Fix', 'myUser'

Again, just to generate the SQL required to do this for all users could you run the following SQL and output the result to text.  You can then run the SQL that is output:

SELECT 'EXEC sp_change_users_login ''Auto_Fix'', '''+ user_id + ''''
FROM staff s
JOIN sys.sysusers su
ON su.name = s.user_id
WHERE s.is_Deleted = 'N'