Safely Remove Old SQL User and Schema

In older versions of Microsoft SQL Server (and Sybase?), a separate database schema was created for each database user. This caused confusion and resulted in non-standard fully qualified object names like jsmith.tabname rather than dbo.tabname.

To correct this, you can assign the dbo default schema to each database user and migrate all objects into the dbo schema. Note that you must migrate all objects out of the schema prior to deleting it.

To find objects in other schemas, use a command like this:

USE [myDatabaseName]
SELECT 
  SCHEMA_NAME(schema_id) AS [schema]
  ,* 
FROM sys.objects 
WHERE 
  SCHEMA_NAME(schema_id) <> 'dbo'
  AND SCHEMA_NAME(schema_id) <> 'sys'

To migrate objects into the dbo schema, use a command like this:

USE [myDatabaseName]
ALTER SCHEMA dbo TRANSFER OldSchema.TableName;

Once the objects have been moved to the dbo schema, you can delete the old schema if desired using “DROP SCHEMA [OldSchemaName]” command. See the following articles for reference and further reading.

Advertisements

About notesbytom

Keeping technology notes on WordPress.com to free up my mind to solve new problems rather than figuring out the same ones repeatedly :-).
This entry was posted in Database and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s