Can We Manage SQL Server 2000 With WMI and Visual Basic.NET? - Can we retrieve all logins available in SQL Server 2000?
(Page 3 of 5 )
Now we come to dealing with SQL Server logins. How do you retrieve the “logins” available in SQL Server 2000 using WMI? Actually it is simpler than you might have expected. Let us go through the following code:
Private Sub btnListLogins_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnListLogins.Click
Dim PrivateLateBoundClass As ManagementClass
PrivateLateBoundClass = New ManagementClass(Nothing, New ManagementPath("\\server\root\MicrosoftSQLServer:
MSSQL_Login"), Nothing)
Dim logins As ManagementObjectCollection = PrivateLateBoundClass.GetInstances()
'loop the information to store in an array
Dim login As ManagementObject
Dim sLogin As String
For Each login In logins
sLogin &= login("SQLServerName").ToString & " - " & login("Name").ToString() & ControlChars.NewLine
Next
MessageBox.Show(sLogin)
End Sub
Now, I am working on the “ManagementClass” rather than the “ManagementObject”. Even though I didn’t specify that it needs to connect to “(local)”, it would automatically get connected to the local database!
“MSQL_Login” is another WMI class I used now (in the above code), to retrieve the entire set of logins existing in the local SQL Server 2000 instance. Since there may exist more than one login, I need to work with the “collection” (which is why I used “ManagementObjectCollection”). The following is the loop, which helps us to retrieve all the “user logins” available in SQL Server 2000 automatically.
Dim logins As ManagementObjectCollection = PrivateLateBoundClass.GetInstances()
'loop the information to store in an array
Dim login As ManagementObject
Dim sLogin As String
For Each login In logins
sLogin &= login("SQLServerName").ToString & " - " & login("Name").ToString() & ControlChars.NewLine
Next
“GetInstances” now would retrieve all “user login” instances. The loop iterates through each “user login” instance, and appends the values of “SQLServerName” and “Name” (user name or login name), which are WMI properties, to the string “sLogin”. Finally, it displays a message box with the string (list of user logins) we framed.
Next: How do you change the password of an SQL Server Login using WMI? >>
More MS SQL Server Articles
More By Jagadish Chaterjee
