Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A bit of background. I have a base application and most clients use it as standard. However some clients have small code and database customisations. Each of these clients has their own branch and maintenance can be tricky.

I want to consolidate all these into a single database structure (not a single database - we aren't doing multi-tenancy) to enable upgrades to be applied in a much more uniform fashion. I'm still at the proof of concept stage, but the route I was going down would be to have the standard objects stay in the schema they currently exist in (mostly dbo) and have the custom objects reside in a schema for each client.

For example, I could have dbo.users and client1.users which has some additional columns. If I set the default schema for the client to be "client1" then the following query

SELECT * FROM users

will return data from the client1 schema or the dbo schema depending on which login is connected. This is absolutely perfect for what I'm trying to achieve.

The problem I'm running into is with Views. I have many views which are in the dbo schema and refer to the Users table. No matter which user I connect to the database as, these views always select from dbo.users.

So I'm guessing the question I have is: Can I prefix the tables in the view with some variable like "DEFAULT"? e.g.

SELECT u.username, u.email, a.level
FROM DEFAULT.users u INNER JOIN DEFAULT.accessLevels a ON u.accessID = a.accessID

If this isn't possible and I'm totally barking up the wrong tree, do you have any suggestions as to how I can achieve what I'm setting out to do?

Many thanks.

share|improve this question
1  
Create multiple views and/or table-valued functions. You can't override the schema without specifying it explicitly unless you build dynamic SQL to do so for you, but you can't do that in a view or a function - only a stored procedure. –  Aaron Bertrand Apr 25 '13 at 0:38
    
That's what I was afraid of. I did some tests and if I take the query out of a view and execute it, each table automatically uses the user's default schema as I had hoped. However as soon as the query is inside the view, it is forced into whichever schema in which the view exists. Thanks! –  Ryan Edgar Apr 25 '13 at 8:52
add comment

2 Answers

up vote 2 down vote accepted

Create multiple views and/or table-valued functions. You can't override the schema without specifying it explicitly unless you build dynamic SQL to do so for you, but you can't do that in a view or a function - only a stored procedure.

With that said, you could do that with something like this, however it will fall back to dbo (or some other schema you hard-code) if a match is not found or if you are using Windows groups for example (which prior to SQL Server 2012 do not support default schema):

CREATE PROCEDURE dbo.whatever_procedure
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX), @s SYSNAME;

  SELECT @s = d.default_schema_name
    FROM sys.database_principals AS d
    LEFT OUTER JOIN sys.server_principals AS s
    ON d.[sid] = s.[sid]
    WHERE SUSER_SNAME() IN (d.name, s.name);

  SELECT @s = COALESCE(@s, N'dbo');

  SET @sql = N'SELECT x FROM ' + QUOTENAME(@s) 
    + '.whatever_view_name;';

  EXEC sp_executesql @sql;
END
GO

You can test this:

USE tempdb;
GO
CREATE SCHEMA foo;
GO
CREATE LOGIN flubat WITH PASSWORD='flubat',CHECK_POLICY = OFF;
GO
CREATE USER flubat FROM LOGIN flubat WITH DEFAULT_SCHEMA = foo;
GO
GRANT SELECT ON SCHEMA::foo TO flubat;
GO
CREATE VIEW dbo.whatever_view_name AS SELECT x = 'dbo';
GO
CREATE VIEW foo.whatever_view_name AS SELECT x = 'foo';
GO
-- create the above procedure
GO
GRANT EXEC ON dbo.whatever_procedure TO flubat;
GO

And then execute it as yourself and also as flubat:

EXEC dbo.whatever_procedure;
GO
EXECUTE AS USER = N'flubat';
GO
EXEC dbo.whatever_procedure;
GO
REVERT;

Results:

x 
----
dbo

x
----
foo

But like I said, this is potentially fragile, and I would lean toward either creating a view per schema, or changing the design altogether.

share|improve this answer
    
Thanks for this - I appreciate your help. It looks like I'm going to have to have a rethink. I really don't want to have to generate about 20 new identical views because of one slightly different table for one client. It's probably the "correct" way to do it, but creates a lot more work from a maintenance perspective which is the exact opposite of what I'm trying to achieve. Thank! –  Ryan Edgar Apr 25 '13 at 15:25
add comment

Nasty little hacks abound here.

Try this one:

create procedure mv_view ( @user as varchar,@view as varchar )
as
declare @sqlstr  nvarchar; 
begin
select @sqlstr = 'select * from ' + 'user.' + @view +';';
sp_executesql @statement = @sqlstr;
end;
share|improve this answer
1  
I don't think even that hack would work. I don't want to have to specify the user - I want the user's default schema to be automatically used for whichever objects are contained in the view. If I don't specify the schema, this works perfectly in a bog-standard query. Put that query into a view however and it no longer functions with that behaviour. –  Ryan Edgar Apr 25 '13 at 8:48
2  
Paddy, please explain why would this work. Thanks! –  dezso Apr 25 '13 at 10:10
    
Yes, there are syntax errors and also why on earth would you ever specify varchar/nvarchar without a length? Do you know how many characters those strings will be truncated to? –  Aaron Bertrand Apr 25 '13 at 15:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.