אפר12
Written by:
ronen ariely
12/04/2019 18:36 
Introduction
This post present code to configure default configuration parameters for each new LOGIN created in the server instance. The code configures parameters like: default database, default language, password will not expire, and password policy will not be checked. The solution presented here is based on using DDL TRIGGER ON CREATE_LOGIN event and is part of an answer I gave on the MSDN forum.
Before You Start!
Note! in the following code you will need to replace the LoginName name with yours. I am using in the code the value "/"
Code
This is a learning code and not for production. Use it on your own responsibility
use master
GO
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'Trig_ConfigureDefaultDatabaseOnCreateLogin_RonenAri')
DROP TRIGGER Trig_ConfigureDefaultDatabaseOnCreateLogin_RonenAri ON ALL SERVER
GO
CREATE or alter TRIGGER Trig_ConfigureDefaultDatabaseOnCreateLogin_RonenAri
ON ALL SERVER FOR CREATE_LOGIN AS
DECLARE @data xml;
DECLARE @LoginName sysname; -- the user that execute the command
DECLARE @New_login sysname;
DECLARE @LoginType NVARCHAR(1000);
SET @data = EVENTDATA();
SET @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)');
SET @New_login = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)');
SET @LoginType = @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(1000)');
-- For security I add filter to be used only on specific LoginName
IF @LoginName = '/' BEGIN
-- You can add filter for only SQL Server LOGIN
IF @LoginType = 'SQL Login' BEGIN
-- You should **NOT** use the following as it is without restrictions and checks
-- ,Since this is SQL Injection issue!
-- I assume that the person who have permission to create LOGIN
-- will not try to Inject abusive query.
-- If this used by someone without permission to create login
-- it should fail in the first step of the transaction
-- since this is trigger, it is part of the transaction of the CREATE LOGIN
DECLARE @CMD NVARCHAR(MAX) = N'
ALTER LOGIN ' + QUOTENAME(@New_login) + ' WITH
DEFAULT_DATABASE=[InternalsTableStructure01],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=ON
'
EXEC sp_executesql @CMD
END
END
GO
Closure
Not a lot to say, except: I hope this is useful for your needs😃