SQL filter to disallow returning of data during a specific time period that repeats n days-CodePudding

Is there a T-SQL function or a way to lock data from being returned during a specific time period that repeats every n days?

So if I have start date 1/03/2022 and end date 3/03/2022 and I set that to repeat every 13 days i.e. lock data again from 14/03/2022 to 16/03/2022. I want that to repeat every 13 days unless they change the repeat frequency.

CodePudding user response：

If I understand your question, you want to prevent users from accessing data during specific intervals and you want to be able to schedule that. Kind of an odd request, but your best bet would be to create an AD group for users you want to manage access. Then you'd want to create a SQLAgent job to disable the group during the specified intervals. I'd use a table to track the "freeze" intervals to make this as low impact as possible

Create your Login and User

USE [master]
GO
CREATE LOGIN [YourDomain\ReadOnlyGroup] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

USE [yourDatabase] 
GO
CREATE USER [YourDomain\ReadOnlyGroup] FOR LOGIN [YourDomain\ReadOnlyGroup]
GO
USE [yourDatabase]
GO
ALTER ROLE [db_datareader] ADD MEMBER [YourDomain\ReadOnlyGroup]
GO

Next create your table and add the latest/last freeze interval start date. We'll use a more current date, but adjust as needed.

USE master
GO

CREATE TABLE LastReadFreeze (LastFreezeDate date)

UPDATE LastReadFree
SET LastFreezeDate='2022-10-11'

Here's the code to validate the today against the freeze interval and reset as needed. You could make this a stored procedure or use it inline. Either way you'll want to create a SQLAgent Job and schedule it to run every morning.

DECLARE 
    @startDate      date,               --First day of a freeze interval
    @endDate        date,               --Last day of a freeze interval; leave blank
    @freezeInterval int = 2,            --Number of days for a freeze interval -1
    @freeReads      int = 13            --Number of days between freeze inervals

--get the start date of the last freeze interval
SELECT @startDate=LastFreezeDate FROM LastReadFreeze

--set the end date based on freeze interval and 
SELECT @endDate=DATEADD(Day,@freezeInterval, @startDate)

--check today against the freeze start and end dates
IF CAST(GETDATE() as date) BETWEEN @startDate and @endDate
BEGIN
    --if today is between those dates, disable the login
    IF (SELECT is_disabled FROM sys.server_principals WHERE name='YourDomain\ReadOnlyGroup')=0
            ALTER LOGIN [YourDomain\ReadOnlyGroup] DISABLE
END
ELSE
BEGIN
    --if today is not between those dates, check to see if it's time to set a new freeze interval based on end date of last freeze and the free reads
    IF CAST(GETDATE() AS DATE) = DATEADD(Day,@freeReads,@endDate) 
    BEGIN
        --reset the start date of the freeze interval if today matches end date of last interval plus free reads
        UPDATE LastReadFreeze
        SET LastFreezeDate=DATEADD(Day,@freeReads,@endDate)
                
        --disable the login 
        IF (SELECT is_disabled FROM sys.server_principals WHERE name='YourDomain\ReadOnlyGroup')=0
            ALTER LOGIN [YourDomain\ReadOnlyGroup] DISABLE
    END
    ELSE
    BEGIN
        --today is not in the freeze interval nor is it time to reset the freeze interval; ensure the login is enabled
        IF (SELECT is_disabled FROM sys.server_principals WHERE name='YourDomain\ReadOnlyGroup')=1
                ALTER LOGIN [YourDomain\ReadOnlyGroup] ENABLE
    END
END