1-2Remember

During an upgrade of Microsoft SQL 2000 to Microsoft SQL server 2005, SQL Server will create a schema for each user in the database. These schema’s need to be cleaned up afterwards. This article helps you in cleaning up these schema’s

In SQL Server 2000 we were also using dbo as the owner of SQL objects such as tables, stored procedures. After the import all users have a default schema which is the same as their username. The following script will generate script which will reset all users to their default “dbo” schema. The users created and used by sql server are ignored. So only users created by the database administrator are scripted.

----------------------------------------------------------–
– Set the default schema for each user to the dbo schema –
– because this was the way of working in SQL2000         –
———————————————————-–

DECLARE @DefaultSchemaCursor AS CURSOR
DECLARE @UserName AS NVARCHAR(128)
DECLARE @CurrentDefaultSchema AS NVARCHAR(128)

SET @DefaultSchemaCursor = CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR  

SELECT   u.name AS [Name]
,        default_schema_name
FROM sys.database_principals AS u
WHERE (u.type in (‘U’, ‘S’, ‘G’, ‘C’, ‘K’))
AND(CAST(CASE WHEN u.principal_id < 5 OR u.principal_id = 16382 OR u.principal_id = 16383 THEN 1 ELSE 0 END AS bit) = 0)
AND default_schema_name <> ‘dbo’
ORDER BY [name]
OPEN @DefaultSchemaCursor
FETCH NEXT FROM @DefaultSchemaCursor INTO @UserName, @CurrentDefaultSchema
WHILE @@FETCH_STATUS = 0
BEGIN   

   PRINT ‘ALTER USER [’ + @UserName + ‘] WITH DEFAULT_SCHEMA = dbo’   

FETCH NEXT FROM @DefaultSchemaCursor INTO @UserName, @CurrentDefaultSchema
END
CLOSE @DefaultSchemaCursor   

DEALLOCATE @DefaultSchemaCursor

After we have set every user back to the default schema of “dbo” we can remove the unwanted schema’s. This can be done using the following script file. And also for this script only schemas created for the non system users are scripted.

-------------------------------------------------–
– Retrieving the Schemas which are user defined –
————————————————-–
DECLARE @SchemaCursor AS CURSOR
DECLARE @SchemaName AS NVARCHAR(128)  

SET @SchemaCursor = CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR   

    SELECT s.name AS [Schema_Name]
    FROM sys.schemas AS s
    WHERE (   

            (    s.schema_id > 4
                and s.schema_id < 16384
            )
            or
            (
                s.schema_id > 16400
            )
          )   

OPEN @SchemaCursor
FETCH NEXT FROM @SchemaCursor INTO @SchemaName
WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT ‘DROP SCHEMA [’ + @SchemaName + ‘]’   

FETCH NEXT FROM @SchemaCursor INTO @SchemaName
END
CLOSE @SchemaCursor
DEALLOCATE @SchemaCursor

The above scripts are executed in the current database context. If we would like to run this script over all databases in the current Instance of the SQL Server we would have to take some more actions. Because the scripts are using the system tables of the current database context this context must be changed for each database. I have accomplished that by creating a cursor which cylces over all user databases and then executes a sql file using “sqlcmd”.

Save this file as “Fix_Upgrade_Users_And_Schemas.sql” to a share which can be accessed by SQL Server.
Then open this script in SQL Server Management Studio. In order to run this script you must have xp_cmdShell enabled in you SQL Server instance. This script contains a couple of parameters which must be set. Please set them before executing this script. The result of executing this script will be that you have a sql-script which can be executed to fix the default schema’s and remove the unwanted schema’s which are created during the upgrade process of SQL 2000 to SQL 2005.

This entry was posted on Thursday, June 28th, 2007 at 3:18 pm and is filed under SQL Server 2005. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.