Missing SQL Server Logs (1 Viewer)

[KitaYama]

This question is an extended question to This post.
Since the nature of the question is different, I preferred to start a new thread.

When I use SMSS to login to sql server, I can see the sql server Logs under Management. ( I have 6 Files, archived included)

If I add a login trigger to the server to restrict users who can login to server, The log files are missing.
SQL Server Logs folder is empty.

If I delete the trigger, log out and log in again, the log files still are there and it also contains all the logs during the trigger too.

Why adding a login trigger hides the log files?


thank you.

This is the login trigger:

CREATE OR ALTER TRIGGER LogonTrigger_For_Audit ON ALL SERVER FOR LOGON
AS
BEGIN
    DECLARE @login NVARCHAR(200)
    SET @login=ORIGINAL_LOGIN()

    IF @login <> 'MyDomaninName\MyUserName'
        BEGIN
            INSERT INTO Master.dbo.tblLogonAudit (UserName, LogonTime,spid)
                VALUES (@login, GETDATE(),@@SPID);
            print @login + ' failed attempted login'
            ROLLBACK
        END
END

 

[SQL_Hell]

I would be extremely careful with logon triggers, if your table gets accidentally deleted, no one will be able to logon to the server including yourself. I would put an IF EXISTS (table) before your insert.

I am not sure why you are experiencing the log file issue, seems like very odd behaviour, perhaps the trigger is somehow logging you on with lesser permissions, can you see the sql agent when the logon trigger is enabled?

 

[KitaYama]

I would put an IF EXISTS (table) before your insert.

At current stage, I'm testing on a test server. If everything is OK and I achieve what I need, I will follow your advice. Thanks.

can you see the sql agent when the logon trigger is enabled?

Since I'm testing, I had to install the Express version of sql server. Express version doesn't include agent.

if your table gets accidentally deleted, no one will be able to logon to the server including yourself.

I really don't need a login trigger. I just need to prevent users to login to sql server WHILE they are able to read/write data to linked table in an Access FE. If you know any possible way, I really appreciate you help. I've discussed this case in This post

Thanks.

 

[gregbowers]

Hello

Check below steps, what you can do:

1. Check Trigger Code: Look at the code of your login trigger to make sure it's written correctly and isn't causing any errors.
2. Test Trigger: Turn off the login trigger temporarily to see if the logs start showing up again. This will help figure out if the trigger is causing the problem.
3. Monitor Error Logs: Check the SQL Server Error Logs for any messages about logging issues. This could give clues about what's going wrong.
4. Check Permissions: Make sure the SQL Server service account has the right permissions to write to the log files and that the log folder has the correct permissions too.
5. Get Help: If you're still stuck, check the SQL Server documentation or contact Microsoft Support for assistance.

To stop users from logging in, you can use permissions to control access. For example, you can remove login permissions for all users except Domain\Administrator and sa using SQL Server Management Studio (SSMS) or Transact-SQL commands.

I hope this helps you solve the problem and manage access to your SQL Server.
Thanks for sharing

Thank you

 

[KitaYama]

Check Trigger Code: Look at the code of your login trigger to make sure it's written correctly and isn't causing any errors.

As I explained in my first post, the trigger works fine. No error.

Test Trigger: Turn off the login trigger temporarily to see if the logs start showing up again. This will help figure out if the trigger is causing the problem.

from my post above:
"If I delete the trigger, log out and log in again, the log files still are there...."

Monitor Error Logs: Check the SQL Server Error Logs for any messages about logging issues

My question is Error logs don't show. Hence I can't check them.

Check Permissions: Make sure the SQL Server service account has the right permissions to write to the log files and that the log folder has the correct permissions too.

I explained that without trigger I see the logs. It means that the service account has the right permission.
A trigger can not change the permissions of a server.

Get Help: If you're still stuck, check the SQL Server documentation

I came here to get help. The documentation is written for experts.

To stop users from logging in, you can use permissions to control access. For example, you can remove login permissions for all users except Domain\Administrator and sa using SQL Server Management Studio (SSMS) or Transact-SQL commands.

If I remove the DomainName\Users from login permission, all linked tables in Access databases fails and don't open.

Thanks for your time though.

 

[kentgorrell]

At current stage, I'm testing on a test server. If everything is OK and I achieve what I need, I will follow your advice. Thanks.


Since I'm testing, I had to install the Express version of sql server. Express version doesn't include agent.


I really don't need a login trigger. I just need to prevent users to login to sql server WHILE they are able to read/write data to linked table in an Access FE. If you know any possible way, I really appreciate you help. I've discussed this case in This post

Thanks.

Tip: for testing you can use the Developer edition of SQL Server rather than Express. It's free but has all the functionality of a paid edtion, it's just the license does not allow for its use in production.