Hello,

I have been working lately with several companies, within Ecommerce, telecommunications and payments industries and they all had a team that needed to work on looking at last week or last month invoicing and ordering from clients.

The issue was that for 2 companies, the business team wanted them to use heavy clients like MS Access or Excel or directly run queries on SSMS.

As a DBA/ database architect, I warned the company that offering the operations team access to such data was very dangerous and could jeopardize performance overall (locks, large queries , etc…). Additionally I wanted to make sure they only accessed the rows and fields they just needed to access.

The solution was quite simple after all.

1. Limit access to one schema
I first created a schema to limit their access to the data. For example: CREATE SCHEMA Operations Authorization DBO. This way, anything I threw on this schema was going to be compliant with a specific set of permissions.

2. Limit permission to a database role
I requested from the systems/infra team to create a group within the active directory and I added the group as login to the SQL server accessed and then added the user as a member of a database role, such as :
CREATE ROLE db_Operations; EXEC sp_AddRoleMember ‘db_Operations’, ‘\‘; GRANT SELECT ON SCHEMA::Operations TO db_Operations;

3. CREATE THE VIEW TO LIMIT FIELDS AND ROWS
To have the best fine-grained permissions, create then a view to limit the permission of the Operations team to select only the fields and rows that they can see. One can also pseudonomize any fields you wish then not to see and/or mask some characters. One can additionally add joins so that the Operations team will not need to join lookup tables nor need to access them.

4. INCREASE PEFORMANCE BY USING DYNAMIC FILTERED INDEX AND DYNAMIC VIEW CREATION
Finally, I found very useful to create an index and maintain indexes and views via a SQL server job to limit the number of rows when we had to deal with rolling windows such as selecting the most recent invoices, orders, transactions (last day, last week, last month).

Here is an example of dynamic creation of index on a table called: Orders.OrderHeader and the field called : CreatedDate, to get the last 7 days rolling:

SET QUOTED_IDENTIFIER ON;
GO
DECLARE @Date DATE = DATEADD(Day, -7, GETDATE())
DECLARE @DateStr VARCHAR(16) = REPLACE(CONVERT(VARCHAR,@Date),’-‘,”)
DECLARE @Stmt VARCHAR(MAX) =

SET LOCK_TIMEOUT 1000
CREATE INDEX FIX_Orderheader_CreatedDate ON Orders.OrderHeader (CreatedDate, AccountId)
INCLUDE (TotalAmount)
WHERE CreatedDate >= ”’+@DateStr+”’
WITH (ONLINE = ON, DROP_EXISTING=ON)
ON [Index]

print @stmt
EXEC (@Stmt)
GO
SET QUOTED_IDENTIFIER ON;
GO
DECLARE @Date DATE = DATEADD(Day, -7, GETDATE())
DECLARE @DateStr VARCHAR(16) = REPLACE(CONVERT(VARCHAR,@Date),’-‘,”)
DECLARE @Stmt VARCHAR(MAX) =

/*
Prerequisites:
Index FIX_Orderheader_CreatedDate
*/
CREATE OR ALTER VIEW Operations.vLast7DaysOrders
AS
SELECT OH.Id, OH.AccountId, AM.MaskedAccountName, OH.CreatedDate, OH.TotalAmount
FROM Orders.OrderHeader OH
JOIN Account.AccountMain AM ON OH.AccountId = AM.Id
WHERE OH.CreatedDate>=”’+@DateStr+”’

print @stmt
EXEC (@Stmt)
GO

This way, you can achieve :
1. good performance and limit the scope of the Operations team in order to avoid large locks and performance degradation. One can even add OPTION (MAXDOP 1) to limit any CPU resource utilization on the usage of the view. By limiting the view with an actual date, one can use the filtered index automatically on the large table.
2. you hide sensitive data from them and limit by rows and fields their permissions.
3. you help the Operations team to get their data with a nice view with joins and straight to whatever fields they only need.