Category: Automation

Today, the first of January 2016 my new business officially starts. For a long time I have been thinking of starting my own business and in the last three months of 2015 I decided to make the big step. The last three months were busy ones, still my day time job and preparing SQLBrander.com B.V. so I could start on this day. Already some work is planned through people I already knew and I have to do work for Conclusion FIT, my former employer. Thanks to those for helping to start my business!

Of course I will keep blogging and hopefully we meet in person someday. My blog articles will always be in English.

A very good, healthy and successful year to all of you!

The website and logo is designed by Paulien Pannekoek, the building of the website is done by Webproof and Annemiek van Bentem helped me out with the texts on the site. Thank you all 🙂

When creating Word documents that should be reused as a template it is easy to use custom Word document properties. Those custom properties can be used as fields in a Word document. So when you need to create a new document with different values there are two ways.

First you can use CTRL + H (find and replace) to search through your document and find and replace values. This may be easy for documents where there are only a few changes needed.

Second you can use custom properties and fields within Word. By changing the custom properties and updating all the fields (CTRL + A and update) it is a very convenient way of changing larger numbers of custom properties.

I am using the second method for some years now but in some situations with a lot of custom document properties the interface is not the easiest way to go. Especially when you have information that needs to go into the Word document that can be created in an Excel sheet.

With PowerShell I am now able to read all the custom properties from an Excel sheet and add or update them to a Word file.

The PowerShell script askes for the Excel file, the Word file and it asks which sheet from the Excel file must be used.

Check the attached script to learn something of PowerShell or use it to your advance.

There is a small bug in PowerShell when it comes to opening and closing Excel files. Therefore you need to save and close all Excel files on your computer before you start the script. Otherwise some data loss may be possible. Sorry for that, I did not find a solution for this small bug on the Internet.

AddCustomDocumentPropertiesFromExcelToWord.zip

Today I got the question if it is possible to create a login trigger that only is triggered on SQL Server login’s (except SA of course)​ that are used from SQL Server Management Studio to connect to a SQL Server instance.

So the first thing you do in cases like this is to check if someone else has solved this already for you. I found the following post​ that had only part of the solution.​

​So I changed the code so that it does only block login attempts from SQL Server Management Studio with a SQL Server account. Domain accounts and domain groups keep working as requested and also SA keeps working.

/****** Object:  DdlTrigger [TR_Block_Logon_Via_Management_Studio_SQL_Login]    Script Date: 08/11/2015 16:47:21 ******/
IF  EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'TR_Block_Logon_Via_Management_Studio_SQL_Login')
DROP TRIGGER [TR_Block_Logon_Via_Management_Studio_SQL_Login] ON ALL SERVER
GO

/****** Object:  DdlTrigger [TR_Block_Logon_Via_Management_Studio_SQL_Login]    Script Date: 08/11/2015 16:47:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [TR_Block_Logon_Via_Management_Studio_SQL_Login]
ON ALL SERVER
FOR LOGON
AS

BEGIN

DECLARE @program_name nvarchar(128)
DECLARE @host_name nvarchar(128)

SELECT @program_name = program_name,
@host_name = host_name
FROM sys.dm_exec_sessions AS c
WHERE login_name <> 'sa'
AND nt_user_name = ''

IF @program_name LIKE '%Management%Studio%'
BEGIN
RAISERROR('This login is for application use only.',16,1)
ROLLBACK;
END
END;
GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [TR_Block_Logon_Via_Management_Studio_SQL_Login] ON ALL SERVER
GO

There is however a way to bypass this solution so it is not completely fool proof. It is meant as a first blocking.