sql server - Can I set a default schema for within a stored procedure?

Question

I'm working on the next update for StackQL.

One thing I want to do is have the ability to query over several releases. So when I loaded the October data, for example, I didn't delete the old September database. It's still out there. In fact, you can even still query it by including the database name like this:

select top 10 * from SO_Sept09..Posts

This will be even more important as they start providing data for ServerFault and SuperUser.

But I don't like having a whole bunch of databases out there to support this. I'd much rather put all the data in the same database and separate each distinct set into it's own schema. But to make this possible, I need to be able to set a default schema as part of the stored procedure that runs the query, based on a parameter passed to the stored procedure that tells it which database the user selected from a future drop down list to appear in the tool bar.

Queries at StackQL are eventually just passed to the exec() function like this:

exec(@QueryText)

Is there anything I can do either in the stored procedure or prepend to the QueryText string (ala USE [DatabaseName]) to set the default schema used in a query?

Answer 1

There are pieces of how to do this in various places here, but not altogether. The way to do this is:

1. Make a unique login & user for each schema

2. Make these users the owners of each different schema.

3. Set each such user's default schema to be the schema that they own.

4. Use the syntax EXECUTE ('sql commands') AS USER = 'schema-owner' to execute your SQL commands in the context of that default schema.

The following script demonstrates this:

--====== Create the Login for the User:
CREATE LOGIN [UserTest1] WITH PASSWORD='whatever', DEFAULT_DATABASE=[TestUsers], DEFAULT_LANGUAGE=[us_english]
GO

--====== Make a User for the Login:
CREATE USER [UserTest1] FOR LOGIN [UserTest1]
GO

--====== Make a Schema owned by the User and default to it:
--        (I assume that you already have the schemas)
CREATE SCHEMA [UserTest1] AUTHORIZATION [UserTest1]
GO
ALTER USER [UserTest1] WITH DEFAULT_SCHEMA=[UserTest1]
GO

--====== Make a sProc in dbo
CREATE PROCEDURE [dbo].[TestSchema_Exec] AS
    SELECT 'executing in schema [dbo]'
GO
--====== Make a similar sProc in New Schema
CREATE PROCEDURE [UserTest1].[TestSchema_Exec] AS
    SELECT 'executing in schema [UserTest1]'
GO

--========= Demonstrate that we can switch Default Schemas:
EXEC('TestSchema_Exec')

EXEC('TestSchema_Exec') AS USER = 'UserTest1'