Have different stored procedures share the same name in SQL Server 2005

Introduction

There might be some cases where one would need to have several versions of the same stored procedure co-exist in one database.

Let’s for example say that we have a “GetData” stored procedure that is already used in production and that we have a new version of that stored procedure that has not yet been fully tested but needs to be available for an early demo, or for tests that require a database with the most recent data. What we could do is just add a suffix to that stored procedure’s name to differentiate it from the production version, like “GetData_alpha”. If it became common practice to have both alpha and production versions in the same database, it would quickly become a pain to systematically have to rename all calls to those alpha procedures in the new code so they refer to the production version and vice-versa. Instead, here is a technique to have two different stored procedures with the exact same base name.

SQL Server 2005 users and schemas

SQL Server 2005 introduced the use of schemas, which are a security feature allowing to attach a set of database objects to a security group, instead of directly linking them to a user. Schemas also are a great way to organize and separate objects into smaller logical groups. For more information about schemas, see http://technet.microsoft.com/en-us/library/ms190387.aspx.

The idea

If you call a stored procedure without specifying its full name [user].[schema].[name], SQL server will automatically append the user name and its default schema. We will use that behavior to our benefit, by creating two users and two schemas (one for alpha and one for production), and attach each version of our procedures to the appropriate schema. To select which version of the procedure needs to be used in our code, all we’ll have to do is to specify the correct user name in the connection string. The procedures calls using the updated connection will then be implicitly redirected.

An example

I’m going to demonstrate this in a short example, using AdventureWorks.

First, let’s create two logins and two users.

USE [AdventureWorks]
GO

CREATE LOGIN prod_user WITH PASSWORD='!@#password';
CREATE LOGIN alpha_user WITH PASSWORD='!@#password';
GO

CREATE USER prod_user FOR LOGIN prod_user
CREATE USER alpha_user FOR LOGIN alpha_user
GO

Next, I’ll create two schemas and assign them as the default schemas for the new users.

USE [AdventureWorks]
GO

CREATE SCHEMA Alpha AUTHORIZATION alpha_user;
ALTER USER alpha_user WITH DEFAULT_SCHEMA = Alpha ;
GO
CREATE SCHEMA Prod AUTHORIZATION prod_user;
ALTER USER prod_user WITH DEFAULT_SCHEMA = Prod;
GO

(I’m not using “Production” as the schema name because there already is a schema with that name in AdventureWorks).

Next, I’ll create two stored procedures, which for the sake of the example will return very different data. Each of them will be attached to one of the newly created schemas, by specifying the schema name in the CREATE PROCEDURE statement.

USE [AdventureWorks]
GO

CREATE PROCEDURE [Prod].[GetData]
AS
BEGIN
	SELECT * FROM Production.Illustration
END
GO

CREATE PROCEDURE [Alpha].[GetData]
AS
BEGIN
	SELECT * FROM Sales.Individual
END
GO

The last thing we need to do is to update permissions for the tables that our procedures read.

USE AdventureWorks
GO

GRANT SELECT ON SCHEMA :: Sales TO alpha_user
GRANT SELECT ON SCHEMA :: Production TO prod_user

If we now impersonate as alpha_user or prod_user, we’ll get different results using the exact same procedure call.

USE [AdventureWorks]
GO

EXECUTE AS USER='prod_user'
GO
EXEC GetData
GO
REVERT
GO

EXECUTE AS USER='alpha_user'
GO
EXEC GetData
GO
REVERT
GO

The SQL code above is the same as changing the User Id attribute on a connection string from a configuration file. That’s it!

Advertisements