Ever since SQL Server 2005 introduced user-schema separation, schemas have received due importance. Most new designs isolate functionality within the same database via the use of schemas. With the growing use schemas, did you ever face a situation wherein you or one of the team members created a table or a stored procedure (i.e. any securable) against the wrong schema?

This post is based on a personal experience I had long eons ago (in our world of information technology, even a span as long as 2 years is pre-historic!). During a deployment, one of the engineers deployed a stored procedure against an incorrect schema. When the application started throwing errors, a DBA friend of mine and I were called to the rescue. While we resolved the issue using old school techniques (drop & recreate), I recently came across a similar need and that’s when I discovered the solution described below.

The problem

Let’s assume that a stored procedure needs to be added to retrieve the pay history for an Employee. A simplified stored procedure would be something like:

/*
THIS SCRIPT IS PROVIDED AS-IS AND WITHOUT WARRANTY
THE SCRIPT IS FOR DEMONSTRATION PURPOSES ONLY
*/
USE AdventureWorks2008R2
GO

CREATE PROCEDURE proc_GetEmployeePayHistory 
    @businessEntityId INT
AS 
BEGIN
    SET NOCOUNT ON

    SELECT Employee.BusinessEntityID,
           Employee.NationalIDNumber,
           Employee.HireDate,
           EmployeePayHistory.PayFrequency,
           EmployeePayHistory.Rate,
           EmployeePayHistory.RateChangeDate,
           Employee.JobTitle
    FROM HumanResources.Employee
    INNER JOIN HumanResources.EmployeePayHistory ON Employee.BusinessEntityID = EmployeePayHistory.BusinessEntityID
    WHERE Employee.BusinessEntityID = @businessEntityId
END
GO

The stored procedure should have been created against the “HumanResources” schema. However, because no schema has been defined in the script above, by default, the procedure would be created in the “dbo” schema, which is incorrect and needs to be fixed.

The solution – ALTER SCHEMA….TRANSFER option

For a stored procedure, it is comparatively easy for us to drop and recreate the procedure against the correct schema. However, for tables and other securable objects, rectifying an incorrect schema may not be that easy. For example, in the case of tables, one might have default data that needs to be re-generated or may have pre-existing data that needs to be migrated to the new table. A generic solution is therefore required.

This generic solution is provided by Microsoft SQL Server in the form of a TRANSFER option as part of the ALTER SCHEMA syntax.

To change the schema of our stored procedure here, we need to run the following script:

/*
THIS SCRIPT IS PROVIDED AS-IS AND WITHOUT WARRANTY
THE SCRIPT IS FOR DEMONSTRATION PURPOSES ONLY
*/
USE AdventureWorks2008R2
GO

ALTER SCHEMA HumanResources
TRANSFER Object::dbo.proc_GetEmployeePayHistory
GO

Doing so transfers the object – dbo.proc_GetEmployeePayHistory to the HumanResources schema.

Advantage

As mentioned earlier, for stored procedures, views and functions it is reasonably easy to drop-and-recreate the objects. However, the story is different in case of tables. Tables contain data, so dropping and recreating them would involve large amounts of I/O, and would therefore be performance intensive and a rather long process.

The use of ALTER SCHEMA…TRANSFER is a metadata update, and therefore is lightning fast – minimal I/O overheads when compared to the old school methods.

Does this work only with stored procedures?

Of course not! The ALTER SCHEMA…TRANSFER works with the following securable entity types:

• Objects
  • Includes tables, stored procedures, views and functions
• Types
• XML Schema Collections

Security Requirements

To use the ALTER SCHEMA…TRANSFER statement, one needs to have:

1. CONTROL permissions on the object AND
2. ALTER permission on the target schema

In addition, if the securable has an EXECUTE AS OWNER specification on it and the owner is set to SCHEMA OWNER, the user must also have IMPERSONATION permission on the owner of the target schema.

NOTE: After the transfer, any permissions applied on the object are lost and will therefore have to be reapplied.

Reference:

• Books On Line page for ALTER SCHEMA – http://msdn.microsoft.com/en-us/library/ms173423.aspx

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!