en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
אוק12

Written by: ronen ariely
12/10/2019 18:36 RssIcon

Introduction

This post present code to configure default configuration parameters for each new LOGIN created in the server instance. The code configure 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 DLL TRIGGER ON CREATE_LOGIN event

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 "<SQL Server Instance name>/<User name>"

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 = '<SQL Server Instance name>/<User name>' 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😃