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?

Solution 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'

Solution 2

Other than modifying @QueryText itself, the only thing I can think of is a user's default schema:

ALTER USER SO_Sept09_Reader WITH DEFAULT_SCHEMA = SO_Sept09

...and then connect as a different user for each schema you want to use. Hackity hack.

But if your query is dynamically constructed anyway (and I'm sure you know why that's often not a great idea), it might be easiest to just add a schema placeholder to the query text, and pass the schema name along with the query to a replacement function.

Solution 3

Another possibility is to generated copies of each SP in each schema, the unmodified table name in an SP refers to tables in the same schema.

Note this doesn't work with dynamic SQL inside such an SP:

CREATE PROCEDURE schema_a.SP
    @somesql AS varchar(MAX)
AS
BEGIN
    EXEC ( @somesql )
END

CREATE PROCEDURE schema_b.SP
    @somesql AS varchar(MAX)
AS
BEGIN
    EXEC ( @somesql )
END

Won't work, because the schema affinity is lost inside the EXEC.

While this:

CREATE PROCEDURE schema_a.SP
AS
BEGIN
    SELECT * FROM tbl -- Will use schema_a.tbl first
END

CREATE PROCEDURE schema_b.SP
AS
BEGIN
    SELECT * FROM tbl -- Will use schema_b.tbl first
END

works fine.

Similarly:

EXEC ( 'EXEC schema_a.SP' )

would obviously work fine.

Comments

[Joel Coehoorn]

Adding a schema placeholder is definitely not an option. The site lets anyone write and run an sql query again the StackOverflow public data dump, and they could write just about anything. Altering user is out as well, as that would cause concurrency issues. But it does give me the idea to maybe have several users and pick the connection string on the fly.

[Michael Petrotta]

Yep, the latter is what I was suggesting; one or more (static) users per schema; switch users (by connecting with matching connection strings) on the fly.

[Joel Coehoorn]

Still a kludge, but I like it better than creating extra users.

[Joel Coehoorn]

It's a thought, but it'd break a lot of my indexing.

[Michael Petrotta]

You can index views, though with a whole host of caveats. msdn.microsoft.com/en-us/library/dd171921.aspx msdn.microsoft.com/en-us/library/ms191432.aspx

[Cade Roux]

I would go with EXECUTE AS as in RBArryYoung's answer since you are already using dynamic SQL. The reason the schema affinity is not working for you is that once you hit the exec, you are losing the context of the schema from the SP which calls exec, just like it loses just about everything else.