SQL SERVER: Server trigger that will allow TF 460 to be enabled per user for their connection only

EnableTraceFlag460ForLogin.sql

/*
- BEWARE: Login triggers can lock down your system if not coded right (Knew that, just learned it the hard way)
- This login running the trigger should be a certificate account, and not have a password. 
	- Can also optionally disable the login. Does not seem to affect trigger execution
- Don't leave the trigger enabled. 
- Don't test modifications on a server that other users are connecting to. 
*/


USE master
GO
IF EXISTS (SELECT * FROM sys.server_principals sp WHERE sp.name = 'ztestuser') BEGIN
	DECLARE @sql VARCHAR(8000) = ''
	SELECT @sql += CONCAT('KILL ', session_id, '; ')
	FROM sys.dm_exec_sessions
	WHERE login_name = 'ztestuser'
	
	-- kill all the sessions the test user is connected to
	EXEC(@sql)
	DROP LOGIN [ztestuser] 
END
GO
-- MAKE SURE TO DROP THIS LOGIN. DEMO ONLY
CREATE LOGIN [ztestuser] WITH PASSWORD='ilovelamp', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

IF  EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'truncated_string_trigger')
	DROP TRIGGER [truncated_string_trigger] ON ALL SERVER
GO


IF EXISTS (SELECT * FROM sys.server_principals sp WHERE sp.name = 'trigger_runner') BEGIN
	DROP LOGIN [trigger_runner]
END
GO
-- this login SHOULD be created from a certificate AND NOT a password. This is for DEMO only
CREATE LOGIN [trigger_runner] WITH PASSWORD='changeme', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
/*	MORE INFO: 
	https://docs.microsoft.com/en-us/sql/t-sql/statements/create-login-transact-sql?view=sql-server-ver15#remarks
	Logins created from certificates or asymmetric keys are used only for code signing. They cannot be used to connect to SQL Server. 
	You can create a login from a certificate or asymmetric key only when the certificate or asymmetric key already exists in master.
*/
GO
-- use a random pwd, WE DONT WANT TO SAVE THIS PWD
DECLARE @sql NVARCHAR(400) = CONCAT('ALTER LOGIN trigger_runner WITH PASSWORD = ''', CAST(NEWID() AS NVARCHAR(256)), '''')
EXEC (@sql)
GO
-- because of the DBCC TRACEON the login has to be sysadmin, otherwise for email we could use minimal rights
ALTER SERVER ROLE sysadmin ADD MEMBER [trigger_runner]
GO
ALTER LOGIN trigger_runner DISABLE
GO

CREATE TRIGGER truncated_string_trigger 
ON ALL SERVER WITH EXECUTE AS N'trigger_runner'
FOR LOGON AS BEGIN
	-- NOTE: THIS ONLY WORKS ON COMPATABILITY LEVELS LOWER THAN 150 (2019)
	-- STEP 1 - Alter the LOGIN string here to have this trace flag enabled for connections by this user
	-- STEP 2 - Enable the trigger
	-- STEP 3 - When done debugging, disable the trigger
	IF ORIGINAL_LOGIN() = 'ztestuser' BEGIN 
		DECLARE @msg VARCHAR(100) = CONCAT('TRIGGER TURNING ON TRACE FLAG 460 FOR SPID = ', @@SPID, ', LOGIN = ', ORIGINAL_LOGIN())
		
		RAISERROR (@msg, 0, 1) WITH NOWAIT 
		EXEC master..xp_logevent @error_number = 60001, @message = @msg, @severity = 'informational';	 

		DBCC TRACEON(460) WITH NO_INFOMSGS;
	END
END
GO

-- ENABLE TRIGGER [truncated_string_trigger] ON ALL SERVER
GO  
DISABLE TRIGGER [truncated_string_trigger] ON ALL SERVER
GO

/*
POWERSHELL TEST CODE:

Clear-Host

Invoke-Sqlcmd -ServerInstance "SERVERNAME" -Database "master" -Username "ztestuser" -Password "ilovelamp" `
    -OutputAs DataRows -Query "select TOP(10) [IsSysadmin] = IS_SRVROLEMEMBER('sysadmin'), * from dbo.spt_values;" | Format-Table


Invoke-Sqlcmd -ServerInstance "SERVERNAME" -Database "master" -Username "ztestuser" -Password "ilovelamp" `
    -OutputAs DataRows -Query "DBCC TRACESTATUS();" | Format-Table

*/

WARNING: Login triggers can cause serious havoc with a system. Make sure you seriously test this trigger out before using it.