Create a Logon Trigger to Restrict sysadmin logins to connect to SQL Server for given Time Interval

I was struggling with unwanted modifications of SQL Server configurations by random SQL Server sysadmin logins who were part of our SQL Server Instance. Every time we cannot monitor what has been changed and who are doing it on your non-prod servers. We generally monitor our production boxes for any unwanted activities. here i will show you how to create a Logon trigger to restrict sysadmin logins to not perform such activities. I will also show you how we can restrict them between a given time interval. Access will be allowed during office hours and restricted during out of office hours.

Logon trigger Overview

Logon Triggers fire in response to a LOGON event. It fires after the authentication phase of logging in finishes, but before the user session is actually established. Logon Triggers are very useful in tracking and restricting login events.

Sometimes few applications require sysadmin privilege to run and if you downgrade this access, application stops working. So, we cannot restrict sysadmin logins of such applications. There is always a scope of security breach if another login has sysadmin access except DBAs on SQL Server Instance.

To fix this issue, I have created a logon trigger that will restrict access of such application logins who are sysadmin on your SQL Server Instance.  We can restrict them to access SQL Server directly through SSMS or any other medium. You need to pass that medium into code just like I have passed Management studio.

Logon Trigger T-SQL Code

Below is the Logon Trigger T-SQL code which will be used to restrict any login for given time frame.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE trigger [logon_rest]
on all server for logon
as begin
declare @Program varchar(128)
declare @systemname varchar(128)
select @Program =PROGRAM_NAME,@systemname=HOST_NAME from sys.dm_exec_sessions as A
where a.session_id=@@SPID
if ORIGINAL_LOGIN() in('sa') /**Login For Which Access Need to restricted **/
and @Program like'%Management%studio' /** Program via access restricted **/
and
(GETDATE()>(dateadd(day, datediff(day, 0, getdate()), 0) + '18:00') /**Last time till access allowed **/
or
GETDATE()<(dateadd(day, datediff(day, 0, getdate()), 0) + '09:00')) /**Start time from access allowed **/
begin
Raiserror ('This is out of office hour',1,1)
rollback;
end
end;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [logon_rest] ON ALL SERVER
GO
Testing

For the testing purpose, I have taken sa account as a potential application login. You can use your identified login which are running with sysadmin rights. Even we can restrict such logins to connect to SQL Server during given time frame.

Here we have disabled sa server level login to connect through SSMS Before 9:00 AM and Post 6:00 PM. That means server login sa cannot connect to SQL Server Instance between given time slot. You can alter the time slot as per your convenience.

Just for example we have tried to login on SQL server through SSMS between given time frame and here you can see the output. I am not able to connect to SQL Server anymore. You can see this in below screenshot. But at the same time you can login to the same SQL Server Instance using other sysadmin or normal login accounts which is available in sys.syslogins.

logon trigger

Even we can track the failed login attempt made on this SQL Server Instance once you can make a database connection using another login. You can check the SQL Server error logs or you can just run sp_readerrorlog stored procedure to display errors. You can see our test login attempt is showing in below screenshot on the server.

Read more about SQL Server Login Issue related Articles:

I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.

Maruti Nandan

I am working at one of the top MNCs and have extensive experience on all versions of SQL Server since 2000,I have good hands on experience onfollowing database features: Database Mirroring, Always ON, Replication, Log Shipping, Geo-Clustering and Performance Tuning and delivered many SQL Server projects on consolidation, upgrades, heterogeneous replication, HA / DR solutions, automation and major performance tuning

You may also like...

1 Response

  1. Maruti Nandan says:

    Thanks a lot and it is very helpful for me..

Leave a Reply

Your email address will not be published. Required fields are marked *