Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 19, 2002

Automating SQL Server Management with WMI (Part 1)

By DatabaseJournal.com Staff


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 X86\Other\WMI 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


See All Articles by Columnist Marcin Policht




MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date