Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By : Donabel Santos
Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By: Donabel Santos

Overview of this book

Table of Contents (21 chapters)
SQL Server 2014 with PowerShell v5 Cookbook
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Preface
Index

Setting up WMI server event alerts


In this recipe, we will set up a simple WMI server event alert for a DDL event.

Getting ready

We will set up an alert that creates a text file with timestamp every time there is a DDL login event (any of CREATE, ALTER, or DROP). We will utilize the WMI provider for server events in this exercise.

These are the values you will need to know:

Item

Value

Namespace (if using default instance)

root\Microsoft\SqlServer\ServerEvents\MSSQLServer

Namespace (if using named instance)

root\Microsoft\SqlServer\ServerEvents\InstanceName

WMI query

SELECT * FROM DDL_LOGIN_EVENTS

DDL_LOGIN_EVENTS Properties (partial list)

SQLInstance
LoginName
PostTime
SPID
ComputerName
LoginType

For WMI events hitting SQL Server, you will also need to ensure that the SQL Server Broker is running on your target database. In our case, we need to ensure the broker is running on the msdb database. We can use the following snippet in T-SQL and run it in SQL Server Management Studio...