How can we automatically replace a table with a view for a specific user?

Question

We currently have some reports that query a specific table, but one of our users wants the ordering to be different. I don't want to go through and fix each of the reports, because that's rather a mammoth task, and only one user has a problem here. Is there a way to have SELECT queries on a base table automagically SELECT from a view table instead of the base table based on the user making the query? It would be a bonus if I can also base it on the program making the connection. We're using MSSQL 2008.

ie. SELECT * FROM myTable would instead be SELECT * FROM myView if the user executing the query was [user] and the program making the connection was [reporting software name].

EDIT: Note: The outer reporting software is an old version of Crystal Reports running some massive ugly queries built by the query builder. Some sample output would be:

Regular Guy:    Other Guy: 

 ItemCode        ItemCode       
 AAAAAA745       AAAAAA745
 AAABBB672       AAACCC657
 AAACCC657       BBBAAA766
 BBBAAA766       BBBCCC383
 BBBBBB838       AAABBB672
 BBBCCC383       BBBBBB838
 CCCBBB883       CCCBBB883

As we want to order all items with SUBSTRING(ItemCode, 4,3) = 'BBB' at the bottom.

Answer 1

You can check these things in sys.dm_exec_sessions, but you have to do it in your (let's hope) stored procedures (you can't redirect a query that specifies a specific table name to a different table based on these variables):

ALTER PROCEDURE dbo.report
AS
BEGIN
  SET NOCOUNT ON;

  IF EXISTS 
  (
    SELECT 1 FROM sys.dm_exec_sessions 
      WHERE program_name = 'program' 
      AND login_name = 'user'
      AND session_id = @@SPID
  )
  BEGIN
    -- do it their way
    RETURN;
  END

-- do it the normal way
END
GO

This is a bad solution though. If one user wants custom ordering, what happens when another user decides they, too, want different ordering? It will happen. Now all your reports are going to have three forks? Why not make ORDER BY a part of the interface?

I'm wondering if you could do something here with schemas, for example if this guy has a different default schema you could write the query such that it doesn't reference a schema. Everyone else will get dbo.viewname and he would get newschema.viewname. But this is messy, the opposite of self-documenting, and if you're going to write code that handles his case, you may as well fix the code the right way.

Plus, as I suggested in my comment, simply referencing a different view or table name doesn't guarantee you'll get a different ordering by doing so. SQL Server still only has to obey a specific order if you've actually stated one in the outer ORDER BY.

Answer 2

You could make the view add a ROW_NUMBER with different ORDER BY for different users and then the outer select (or your reporting tool's sort setting) would always (explicitly) sort by that new ORDERBYTHIS column.

i.e. make the view as:

SELECT columns, ROW_NUMBER() OVER(ORDER BY special columns)
FROM whatever
WHERE -- user = special user 
UNION ALL
SELECT columns, ROW_NUMBER() OVER(ORDER BY regular columns)
FROM whatever
WHERE -- user <> special user

http://msdn.microsoft.com/en-us/library/ms186734.aspx

You would still need to set each report to ORDER BY this new column.

Probably not a great performer, but who knows without checking the execution plans.