server side triggers - how to prevent a linked server creation?

Question

This Trigger actions on database creation - shows an example of a server side trigger that is fired when someone creates a new database.

This other question - Trigger to change Database collation on creation - also shows an example of a server side trigger that is fired when someone creates a new database.

I have here an example of a server side trigger that prevents users of dropping logins:

USE [master]
GO
--======================================================================
-- example of a server trigger - does not allow a login to be dropped
create trigger [no_dropped_logins]   on all server

for drop_login

as

insert into ##LOGIN_WATCH

select r.*, s.login_name, s.host_name,

EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') AS 'CmdText'

from sys.dm_exec_requests r

inner join sys.dm_exec_sessions s

on r.session_id = s.session_id

where r.session_id = @@SPID

My question is:

how can I prevent a linked server creation in sql server using server side triggers?

Answer 1

this is working in one of my dev servers:

IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'ddl_trig_DDL_linked_servers')
BEGIN
    DROP TRIGGER ddl_trig_DDL_linked_servers
    ON ALL SERVER;
END
GO
CREATE OR ALTER TRIGGER ddl_trig_DDL_linked_servers 
ON ALL SERVER 
FOR CREATE_LINKED_SERVER,ALTER_LINKED_SERVER
AS
SET NOCOUNT ON;
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL ON;
DECLARE @data XML
,@ErrMsg NVARCHAR(1000)
SET @data = EVENTDATA()
SELECT @ErrMsg = LEFT(
  CONVERT(NVARCHAR(100), ORIGINAL_LOGIN()) + ' - '

COALESCE(@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),'') + ' -  '
COALESCE(CONVERT(NVARCHAR(100), HOST_NAME()),'') + ' - '
'Cannot be created or modified!',1000)

;THROW 60000, @ErrMsg, 1
GO
--======================================================================
-- disabling and enabling a trigger
--DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }

--ON { object_name | DATABASE | ALL SERVER } [ ; ]
-- https://docs.microsoft.com/en-us/sql/t-sql/statements/disable-trigger-transact-sql
DISABLE trigger ddl_trig_DDL_linked_servers ON ALL SERVER;
ENABLE trigger ddl_trig_DDL_linked_servers ON ALL SERVER;