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
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.
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:
- How to fix Error 15173: Revoke the permissions before dropping the login.
- Fix error 15138 & Error 3729: DROP login failed
- How to fix error 15434: Could not drop login as the user is currently logged in
- Fixing error 15170: Login owns one or more SQL Agent Jobs