SXI Forum

StephanB

Member

Registered: 21-11-2018

Posts: 39

Limit Access to DB by Time and Day with a Logon Trigger (Asset)

There are many situations in production when you may want to limit user access to SQL Server during times, when Assets are update to a Data Base.
For instance, say a particular login should not be connecting to the database on weekends when the Asset update are running.
The way to go about this is with a Logon Trigger, here is the definition of a Logon Trigger straight from MSDN:

Logon Trigger
Logon Triggers fire stored procedures in response to a LOGON event.
This event is raised when a user session is established with an instance of SQL Server.
Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established.

A Logon Trigger is really just a DDL Trigger, and two popular functions of a Logon Trigger are for auditing and preventing connections.
We are going to focus on the latter with this post in order to limit particular logins from connecting to the instance during certain times of the day or certain days altogether.

The T-SQL code will consist of three objects:

• A table to contain the login and deny time parameters.

• A stored procedure to add the data to that table.

• The Logon Trigger code.

Login Deny Time Table
This is the table that will house the times/days per login that will be denied access to the Data Base. 
The Logon Trigger will use this table to reference if the connecting login should be permitted to successfully connect.

USE [master]
GO

if object_id('server_login_admission') is NOT NULL
      DROP TABLE dbo.server_login_admission
GO
CREATE TABLE dbo.server_login_admission
(
      admission_id int IDENTITY(1, 1) not null primary key clustered,
      login_name nvarchar(256) NOT NULL,
      deny_day int NOT NULL
            check (deny_day between 1 and 7),
      deny_time_begin time NULL,
      deny_time_end time NULL,
      deny_full_day bit NOT NULL default 0
)
GO

ALTER TABLE dbo.server_login_admission
add constraint CK_TimeOrFullDay check
(
      (
            deny_time_begin is NOT NULL
            and deny_time_end is NOT NULL
      )
      or deny_full_day = 1
)
GO

ALTER TABLE dbo.server_login_admission
add constraint CK_DenyTimeNullHandling check
(
      (
            deny_time_begin is NULL
            and deny_time_end is NULL
      ) or
      (
            deny_time_begin is NOT NULL
            and deny_time_end is NOT NULL
      )
)
GO

alter table dbo.server_login_admission
add constraint CK_DenyTimeRelativity check
(
      deny_time_begin < deny_time_end
      or
      (
            deny_time_begin is NULL
            and deny_time_end is NULL
      )
)
GO

Add Login Deny Data Stored Procedure
This is the stored procedure that will be called to enter data into the table.
A quick review is that Sunday = 1, Monday = 2, so on and so forth, Saturday = 7.

USE [master]
GO

if object_id('dbo.sp_add_server_login_admission') is not null
      DROP PROCEDURE dbo.sp_add_server_login_admission
GO

CREATE PROCEDURE dbo.sp_add_server_login_admission
      @login_name nvarchar(256),
      @deny_day int,
      @deny_time_begin time = NULL,
      @deny_time_end time = NULL,
      @deny_full_day bit = 0
AS

      set nocount on;
     
      -- check to make sure the login actually exists
      if suser_id(@login_name) is NULL
            begin
                  raiserror
                  (
                        'Unknown login name',
                        16,
                        1
                  )
                  return -1
            end
     
      -- make sure the @deny_day is a valid day of the week
      if @deny_day not between 1 and 7
            begin
                  raiserror
                  (
                        'Invalid deny day',
                        16,
                        1
                  )
                  return -1
            end
           
      if
      (
            @deny_time_begin is NULL
            and @deny_time_end is NOT NULL
      ) or
      (
            @deny_time_begin is NOT NULL
            and @deny_time_end is NULL
      )
            begin
                  raiserror
                  (
                        'Both deny time parameters must have a value,
                        or both must be NULL',
                        16,
                        1
                  )
                  return -1
            end
           
      -- ensure @deny_time and @deny_full_day aren't null and 0
      if @deny_time_begin is NULL
      and @deny_full_day = 0
            begin
                  raiserror
                  (
                        'Deny time cannot be NULL
                        if login is not denied for a whole day',
                        16,
                        1
                  )
                  return -1
            end
           
      insert into dbo.server_login_admission
      (
            login_name,
            deny_day,
            deny_time_begin,
            deny_time_end,
            deny_full_day
      )
      values
      (
            @login_name,
            @deny_day,
            @deny_time_begin,
            @deny_time_end,
            @deny_full_day
      )
     
GO

Logon Trigger
As you can see, this code simply does a check on the server_login_admission table to see if the connecting login is not denied to connect at the current time of the current day.

USE [master]
GO

if exists
(
      select *
      from master.sys.server_triggers
      where name = 'logon_trigger_deny_by_time'
)
      drop trigger logon_trigger_deny_by_time
      on all server
GO

create trigger logon_trigger_deny_by_time
on all server
with execute as self
for logon
as

      declare
            @current_login nvarchar(256),
            @current_weekday int,
            @current_time time
           
      select
            @current_login = original_login(),
            @current_weekday = datepart(dw, getdate()),
            @current_time = cast(getdate() as time)
           
      if exists
      (
            select *
            from master.dbo.server_login_admission
            where login_name = @current_login
            and deny_day = @current_weekday
            and
            (
                  @current_time between deny_time_begin and deny_time_end
                  or deny_full_day = 1
            )
      )
            begin
                  rollback
            end

GO

Here is how you can deny login access for a specific user executing the following Procedure:
Sunday = 1, Monday = 2, so on and so forth, Saturday = 7

Denies User1 from connecting to the Data Base on Monday the whole day.

EXEC dbo.sp_add_server_login_admission
      @login_name = 'User1', -- This is the user you want to block
      @deny_day = 2,         -- deny user access on Mondays
      @deny_full_day = 1     -- deny the user for the whole day
GO

Denies User1 from connecting to the Data Base on Saturday from 5:00pm to 11:00pm.

EXEC dbo.sp_add_server_login_admission
      @login_name = 'User1', -- This is the user you want to block
      @deny_day = 7,              -- deny user access during Saturday
      @deny_time_begin = '17:00', -- deny user access form 5:00pm
      @deny_time_end = '23:00'    -- deny user access To 11:00pm
GO

Last edited by StephanB (30-03-2020 21:34:20)