Windows Management Instrumentation is becoming one of the prevalent management technologies in Windows environments. Although it is primarily known among Windows server and desktop administrators, its tremendous versatility encompasses other management areas as well. The goal of this series of articles is to demonstrate the use of WMI in SQL Server management.
The architecture of WMI allows extensibility through the use of providers. A provider is a piece of code (typically in the form of a Dynamic Link Library file) that interfaces between WMI and a software or hardware component. The subcomponents of each component are represented by classes. For example, in the case of SQL Server, there is a class representing a database, a stored procedure, a view, etc. Classes, however, are just definitions of these subcomponents. Actual subcomponents are represented by instances of a class. For example, if a SQL Server hosted ten databases, then there would be ten instances of the database class.
Classes are also arranged into namespaces. Namespaces serve three functions:
- Simplify searches since related classes are grouped together,
- Eliminate naming clashes (in case two distinct classes have the same names),
- Provide security boundaries (permissions can be assigned on the namespace, not class, level).
The ability to manage SQL server is implemented as the SQL Server provider. The SQL Server provider is not installed by default, but it is included with the SQL Server 2000 source files in the X86OtherWMI folder. It can be used to manage both SQL Server 2000 and 7.0. To install it, simply run the SQLWMI80.MSI file. This will also create the root/MicrosoftSQLServer namespace and populate it with definitions of all SQL Server classes. Note that you only need to install the provider on the SQL server itself, but you can connect to it from any Windows system that has WMI components present.
Each class is described by its properties. For example, the MSSQL_Database class has properties such as Name, DatabaseStatus, PrimaryFilePath, or CompatibilityLevel. Besides properties, classes can also have methods, which affect state of class instances. Use of the methods relevant to SQL server administration is the focus of this article.
Let’s start with the most basic SQL Server operations — starting and stopping SQL Server. The MSSQL_SQLServer class, representing SQL Server, contains two methods, named appropriately Start and Stop, which will be used in the two scripts below. With SQL Server 2000, it is possible to install multiple instances of SQL Server on a single computer, which means that we can have multiple instances of the MSSQL_SQLServer class. Each instance is identified by a unique name (the default instance is automatically assigned the same name as the computer). This is the reason that in each of the scripts below you need to provide both the computer name and SQL Server instance name.
Script to Start SQL Server Instance
To start the default instance of the SQL server on the computer SWYNKSRV01, you would execute the following script (for other instances, you would need to change the value of the sSQLServer variable):
sComputer = "SWYNKSRV01" sSQLServer = "SWYNKSRV01" Set oInstance = GetObject("winmgmts:{impersonationLevel=impersonate}//" & _ sComputer & "/" & "root/MicrosoftSQLServer:MSSQL_SQLServer.Name=" & _ Chr(34) & sSQLServer & Chr(34)) Set oOutParam = oInstance.Start() If oOutParam.ReturnValue = 0 Then WScript.Echo sSQLServer & " SQL Server started successfully" Else WScript.Echo "Starting of " & sSQLServer & " SQL Server failed with error " & _ oOutParam.Description End If
Script to Stop SQL Server Instance
The script used to stop the same instance of the SQL server would look very similar:
sComputer = "SWYNKSRV01" sSQLServer = "SWYNKSRV01" Set oInstance = GetObject("winmgmts:{impersonationLevel=impersonate}//" & _ sComputer & "/" & "root/MicrosoftSQLServer:MSSQL_SQLServer.Name=" & _ Chr(34) & sSQLServer & Chr(34)) Set oOutParam = oInstance.Stop() If oOutParam.ReturnValue = 0 Then WScript.Echo sSQLServer & " SQL Server stopped successfully" Else WScript.Echo "Stopping of " & sSQLServer & " SQL Server failed with error " & _ oOutParam.Description End If
Map a User Name to a Login Name Script
Next, let's take a closer look at the MSSQL_Login class. As the name indicates, this class deals with SQL logins. The script that follows displays a user name within a specified database that maps to a given login name. To determine a user name for another database or login, you would need to modify the values of the sLogin and sDbName variables.
sComputer = "SWYNKSRV01" sSQLServer = "SWYNKSRV01" sLogin = "SwynkLogin" sDbName = "pubs" Set oInstance = GetObject("winmgmts:{impersonationLevel=impersonate}//" & _ sComputer & "/" & "root/MicrosoftSQLServer:MSSQL_Login" & _ ".SQLServerName=" & Chr(34) & sSQLServer & Chr(34) & _ ",Name=" & Chr(34) & sLogin & Chr(34)) sRetVal = oInstance.GetUserName(sDbName, objOutParam) WScript.Echo "The login " & sLogin & " is referenced in the " & sDbName & _ " database as the user " & sRetVal
Change Login Password Script
Our final script uses the SetPassword method of the MSSQL_Login class to change the password for a specified login. Note that this is a password change, not a reset, since it requires providing both old and new passwords.
sComputer = "SWYNKSRV01" sSQLServer = "SWYNKSRV01" sLogin = "SwynkLogin" sOldPassword = "0ldPa$$w0rd" sNewPassword = "NuPa$$w0rd" Set oInstance = GetObject("winmgmts:{impersonationLevel=impersonate}//" & _ sComputer & "/" & "root/MicrosoftSQLServer:MSSQL_Login" & _ ".SQLServerName=" & Chr(34) & sSQLServer & Chr(34) & _ ",Name=" & Chr(34) & sLogin & Chr(34)) Set oOutParam = oInstance.SetPassword(sOldPassword, sNewPassword) If oOutParam.ReturnValue = 0 Then WScript.Echo "New password for " & sLogin & " set successfully" Else WScript.Echo "Password change for " & sLogin & " failed with the error " & _ oOutParam.Description End If
