June 12, 2014 at 7:34 am
Hello,
If I disable a SQL login (GlobalAccess), will the server object trigger (GlobalAccess_Track_Trigger) still function by inserting a record to the table?
CREATE TRIGGER [GlobalAccess_Track_Trigger]
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'GlobalAccess'
BEGIN
INSERT INTO MyDatabase.dbo.TrackGlobalAccess (HostName, AppName, LDate)
VALUES (HOST_NAME(), APP_NAME(), GETDATE());
END -- IF ORIGINAL_LOGIN()= 'GlobalAccess'
END; -- Trig
-- End Trig
GO
We are trying to get the last none windows authentications handled. You got to love legacy systems.
Thank you,
June 12, 2014 at 10:15 am
Not sure that I understand the question. If you disable the login, there will not be any logins to record in the table...
And for that matter, nor will any logins be recorded if the login does not have write permission to the table. The login will fail in this case. You would need to have an EXECUTE AS clause for the login that owns the database with the log table.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
June 12, 2014 at 10:20 am
If a procedure tries to access the database using SQL authentication but the login is disabled will the trigger then log the try if the trigger is modified to "EXECUTE AS" a valid user with needed permissions?
June 12, 2014 at 10:29 am
I'm afraid that I don't understand the question. If you are running a stored procedure, the logon trigger till not fire, because you are already logged in.
But if you attempt to log in with a disabled login, no this will not be recorded in by the trigger, because the trigger will not fire, since the login fails.
If you want to see failed logins for this GlobalLogin, you should make sure that SQL Server log failed login attempts to the SQL Server error log. (You do that in Object Explorer, right-click the server node, select Properties and then the Security page.)
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
June 12, 2014 at 10:36 am
Erland Sommarskog (6/12/2014)
I'm afraid that I don't understand the question. If you are running a stored procedure, the logon trigger till not fire, because you are already logged in.But if you attempt to log in with a disabled login, no this will not be recorded in by the trigger, because the trigger will not fire, since the login fails.
If you want to see failed logins for this GlobalLogin, you should make sure that SQL Server log failed login attempts to the SQL Server error log. (You do that in Object Explorer, right-click the server node, select Properties and then the Security page.)
Thanks Erland, I should have said process not procedure, thus the attempted login part is the answer.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply