Limit Instance Access by Time and Day with a Logon Trigger

                There are many situations in production when you may want to limit user access to SQL Server during off-hour times.  For instance, say a particular login should not be connecting to the database on weekends.  The obvious way to go about this is with a Logon Trigger.  In review, 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, and of course the Logon Trigger code.
                Login Deny Time Table – this is the table that will house the times/days per login that they will be denied access.  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 tabledbo.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
      Notice the multiple CHECK constraints.  These are in place to ensure proper and appropriate data manipulation.
                Add Login Deny Data Stored Procedure – this is the stored procedure that will be called to enter data into the aforementioned table.  It is worth noting that a parameter for this stored procedure is the weekday enumeration.  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 proceduredbo.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 nocounton;
     
      — 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 intodbo.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 – and last, but surely not least is the actual Logon Trigger.  As you can see, this code simply does a check on the server_login_admissiontable 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 triggerlogon_trigger_deny_by_time
      on allserver
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 betweendeny_time_begin and deny_time_end
                  or deny_full_day = 1
            )
      )
            begin
                  rollback
            end
go
      Usage – here is some ways you can deny login access:
exec dbo.sp_add_server_login_admission
      @login_name = ‘SomeLogin1’,
      @deny_day = 2,          — deny user access on Mondays
      @deny_full_day = 1      — deny the user for the whole day
go
exec dbo.sp_add_server_login_admission
      @login_name = ‘SomeLogin2’,
      @deny_day = 7,          — deny user access during Saturday
      @deny_time_begin = ’17:00′,
      @deny_time_end = ’23:00′
go
                The above code (provided the Logon Trigger is created and enabled) denies SomeLogin1from connecting to the instance on Monday (throughout the whole day).  It also denies SomeLogin2 from connecting to the instance on Saturday from 5:00pm to 11:00pm.
                Change the code, make it fit your particular environment’s demands, have fun with it.  The above code should give you a great starting point in the adventure to appropriate security in regards to logins and access times.
Source Code:
                The above version-controlled T-SQL can be found on my SQLSalt GitHub repository:
                If you have any questions, problems, or comments please feel free to leave a comment below or email me at sqlsalt@gmail.com.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s