PowerShell and SQL Server Events–Monitoring Schema Changed


Yes, we can monitor all changes to the event schema using AUDIT_SCHEMA_OBJECT_MANAGEMENT_EVENT

All Schema Changes :

$query = "select * from AUDIT_SCHEMA_OBJECT_MANAGEMENT_EVENT"

Schema Changed by Alter

$query = "select * from AUDIT_SCHEMA_OBJECT_MANAGEMENT_EVENT where EventSubClass = 2"

in the specific database

$query = "select * from AUDIT_SCHEMA_OBJECT_MANAGEMENT_EVENT where EventSubClass = 2 and databaseid = 10 "

And Object

$query = "select * from AUDIT_SCHEMA_OBJECT_MANAGEMENT_EVENT where EventSubClass = 2 and databaseid = 10 and ObjectName = 'YourTable' "

I did some changes in the module to be reusable then :

Get-SPRecompile changes to Get-ClassProperties passing by parameter 1 or 2 . 1 = SPRecompile  and 2 Schema Changed. Plus other functions to work with each Event Class  and the name now is SQLEvents.psm1.

Download Module

Create the Table SchemaChanged

CREATE TABLE [dbo].[SchemaChanged](

    [ApplicationName] [nvarchar](max) NULL,

    [ClientProcessID] [int] NULL,

    [DatabaseID] [int] NULL,

    [DatabaseName] [nvarchar](max) NULL,

    [EventSubClass] [nvarchar](50) NULL,

    [HostName] [nvarchar](max) NULL,

    [IsSystem] [int] NULL,

    [LoginName] [nvarchar](max) NULL,

    [NTDomainName] [nvarchar](max) NULL,

    [NTUserName] [nvarchar](max) NULL,

    [ObjectID] [int] NULL,

    [ObjectName] [nvarchar](max) NULL,

    [ObjectType] [nvarchar](max) NULL,

    [RequestID] [int] NULL,

    [ServerName] [nvarchar](max) NULL,

    [SessionLoginName] [nvarchar](max) NULL,

    [SPID] [int] NULL,

    [StartTime] [datetime] NULL,

    [TextData] [nvarchar](max) NULL

) ON [PRIMARY]

and register the event :

#

$query = "select * from AUDIT_SCHEMA_OBJECT_MANAGEMENT_EVENT "

Register-WMIEvent -Namespace 'root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'  -Query $query  -Action {ipmo sqlevents ;ipmo functions;Get-ClassProperties $event.SourceEventArgs.NewEvent 2 | Out-DataTable | write-datatable -ServerInstance Vader -Database test -TableName SchemaChanged}

 

 

Now Its is just change some schema in the server and watch the magic Alegre (again)

Watch the Video

#PowerShellLifeStyle

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s