Working with the WMI Provider for Server Events
This topic provides guidelines you should consider before you program using the WMI Provider for Server Events.
Enabling Service Broker
The WMI Provider for Server Events works by translating WQL queries for events into event notifications in the database that you target. An understanding of how event notifications work can be useful to you when programming against the provider. For more information, see Event Notifications (Database Engine).
In particular, because the event notifications created by the WMI Provider use SQL Server to send messages about server events, this service must be enabled wherever the events are generated. If your program queries events on a server instance, the Service Broker in msdb of that instance must be enabled, because that is the location of the target Service Broker service (named SQL/Notifications/ProcessWMIEventProviderNotification/v1.0) that is created by the provider. If your program queries events in a database or on a particular database object, the Service Broker in that target database must be enabled. If the corresponding Service Broker is not enabled after your application is deployed, any events generated by the underlying event notification are sent to the queue of the service used by the event notification, but are not returned to your WMI management application until Service Broker is enabled.
The following query determines which service brokers are enabled on a server instance, and the broker instance GUID:
SELECT name, is_broker_enabled, service_broker_guid FROM sys.databases;
The service broker GUID of msdb is of particular interest because that is the location of the target service of the provider.
To enable Service Broker in a database, use the ENABLE_BROKER SET option of the ALTER DATABASE statement.
Specifying a Connection String
Applications direct the WMI Provider for Server Events to an instance of SQL Server by connecting to a WMI namespace defined by the provider. The Windows WMI service maps this namespace to the provider DLL, Sqlwep.dll, and loads it into memory. Each instance of SQL Server has its own WMI namespace, which defaults to: \\.\root\Microsoft\SqlServer\ServerEvents\instance_name. instance_name defaults to MSSQLSERVER in a default installation of SQL Server.
Permissions and Server Authentication
To access the WMI Provider for Server Events, the client on which a WMI management application originates must correspond to Windows authenticated login or group in the instance of SQL Server specified in the application's connection string of the application.
Permissions and Event Notification Scope
The WMI Provider for Server Events translates WQL queries into event notifications in the target database. Because of this, the calling application must have not only the required minimum permissions to access the provider, but must also have the correct permissions in the database to create the required event notifications. The following are the permissions:
To create an event notification that is scoped to the database, at a minimum, CREATE DATABASE DDL EVENT NOTIFICATION permission in the current database is required.
To create an event notification on a DDL statement that is scoped to the server, at a minimum, CREATE DDL EVENT NOTIFICATION permission in the server is required.
To create an event notification on a trace event, at a minimum, CREATE TRACE EVENT NOTIFICATION permission in the server is required.
To create an event notification that is scoped to a queue, at a minimum, ALTER permission on the queue is required.
For information about how WQL queries are scoped, see Using WQL with the WMI Provider for Server Events.
To illustrate scope, consider a WMI Provider application that includes the following WQL query:
SELECT * FROM ALTER_TABLE
WHERE DatabaseName = "AdventureWorks2008R2"
AND SchemaName = "Production"
AND ObjectName = "Product"
AND ObjectType = "TABLE"
The WMI provider translates this query into an event notification that is created in the AdventureWorks2008R2 database. This means that the caller must have the required permissions to create such an event notification, specifically CREATE DATABASE DDL EVENT NOTIFICATION permission in the AdventureWorks2008R2 database.
If a WQL query specifies an event notification scoped at the server level, for example by issuing the query SELECT * FROM ALTER_TABLE, the calling application must have the server-level CREATE DDL EVENT NOTIFICATION permission. Note that server-scoped event notifications are stored in the master database. You can use the sys.server_event_notifications catalog view to see their metadata.
Note
The scope of the event notification that is created by the WMI Provider (server, database, or object) ultimately depends on the outcome of the permissions verification process that is used by the WMI provider. This is affected by the permission set of the user that is calling the provider and on the verification of the database that is being queried.
In the previous example, the provider first tries to create an event notification scoped to the database (ON DATABASE). If the provider verifies that the database exists and that the caller has the required permissions to create an event notification on it, the registration is successful. If it is not successful, the provider tries to create an event notification on the server (ON SERVER). Assuming that this attempt is successful, all ALTER_TABLE events that occur on the server are sent from the SQL Server process to the WMI Service process. However, the provider filters out any events that do not apply to the AdventureWorks2008R2 database. Although this process potentially increases the amount of network traffic necessary for the scope of the event, this process also enables you with the flexibility to register WQL queries on databases before they are created, and then receive event data after the database is created and DDL activity starts on it.
Permissions and Message Verification
The WMI Provider does not send messages for event notifications if both of the following conditions are true:
The user that created the event notification through the WMI Provider no longer exists in the database, or no longer has the required permission to create a similar event notification.
The event notifications are created on the following events:
DROP_LOGIN
ALTER_LOGIN
DROP_USER
ALTER_USER
ADD_ROLE_MEMBER
DROP_ROLE_MEMBER
ADD_SERVER_ROLE_MEMBER
DROP_SERVER_ROLE_MEMBER
DENY or REVOKE (Applies only to ALTER DATABASE, ALTER ANY DATABASE EVENT NOTIFICATION, CREATE DATABASE DDL EVENT NOTIFICATION, CONTROL SERVER, ALTER ANY EVENT NOTIFICATION, CREATE DDL EVENT NOTIFICATION, OR CREATE TRACE EVENT NOTIFICATION permissions.)
Working with Event Data on the Client Side
After the WMI Provider for Server Events creates the required event notification in the target database, the event notification sends event data to the target service in msdb that is named SQL/Notifications/ProcessWMIEventProviderNotification/v1.0. The target service puts the event into a queue in msdb that is named WMIEventProviderNotificationQueue. (Both the service and the queue are dynamically created by the provider when it first connects to SQL Server.) The provider then reads the XML event data from this queue and transforms it into managed object format (MOF) before returning it to the client application. The MOF data is made up of the properties of the event that is requested by the WQL query as a Common Information Model (CIM) class definition. Each property has a corresponding CIM type. For example, the SPID property is returned as CIM type Sint32. The CIM types for each property are listed under each event class in WMI Provider for Server Events Classes and Properties.