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 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
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 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 – 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_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
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 SomeLogin1
from 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:
Base deny admission table: LogonTrigger_1_CreateLoginAdmissionTable.sql
Add admission stored proc: LogonTrigger_2_CreateAddLoginPermittedTimeStoredProcedure.sql
Logon Trigger: LogonTrigger_3_CreateLogonTrigger.sql
If you have any questions, problems, or comments
please feel free to leave a comment below or email me at sqlsalt@gmail.com.
No comments:
Post a Comment