Use disconnected recordsets to create and maintain a database that contains a history of SIDs
| Executive Summary:|
Disconnected recordsets are ActiveX Data Objects (ADO) recordsets that reside only in memory while they're active. Because they're in memory, accessing data within them can be quick and efficient. The script UpdateSIDdb.vbs demonstrates how to create and use disconnected recordsets. You can use this VBScript script to create and update a low-maintenance database that holds SID history information for group and user objects in a domain.
For those of you familiar with the '60s, you probably surmised that the title of this article is a spinoff of a phrase coined by the late Dr. Timothy Leary: "Turn On, Tune In, Drop Out." Paraphrasing Dr. Leary, "Turn On" means to delve within and trigger your neural and genetic systems, "Tune In" means to interact harmoniously with the world around you, and "Drop Out" means to be self-reliant.
Rest assured that I'm not going to discuss what was going on back in the '60s, but in a sense, the phrase holds meaning for scripters, even if only in a very imaginative way. For scripters, "Turn On" means to activate your computer system, "Tune In" means to focus on the data you want to acquire, and "Disconnect" means to detach or become independent of any live data source.
The detached data sources that I’m referring to are called disconnected recordsets. They're ActiveX Data Objects (ADO) recordsets that reside only in memory while they're active. Because they're in memory, accessing data within them can be quick and efficient. My experience is that these types of databases are most efficient for small databases composed of less than 10 fields and under 10,000 records. Although you're limited to how large these databases can be by the amount of memory you have available on your system, disconnected recordsets provide high-performance access to data with low memory-overhead and small disk-space footprints.
To create a disconnected recordset, you create an ActiveX Data Objects Recordset (ADOR) object using a command such as
Set DRS = CreateObject("ADOR.Recordset")
ADOR is a subset of the ADO Database (ADODB) Library. The main difference between the two is that the ADODB Library contains additional server side objects (i.e., the Connection, Command, Error, and Parameters objects) that you can use to connect to, communicate with, and manipulate an active database. ADOR is a lightweight ADO client that exposes only the Recordset interface so that you can create and manipulate recordsets that aren't connected to an active database. If you have the ability to create ADO instances on your system, then you have the ability to create ADOR instances as well because ADOR is a subset of ADODB, which in turn is a subset of ADO.
I used ADOR in the script UpdateSIDdb.vbs, which creates and updates a low-maintenance database that holds SID history information for group and user objects in a domain. The first time you run this script, it creates and populates the SID database. Each time you run it thereafter, it adds information about domain group objects and user objects that don’t already exist in the database.
I run UpdateSIDdb.vbs daily as scheduled task. I use the database primarily as a tool to retrieve a user's or group's ID whenever I have only a SID. You can find yourself with only a SID in several situations, including:
- When you’re looking at a file's owner in the file's security properties and the user or group that owns the file no longer exists. In other words, that user or group has likely been deleted.
- When you're looking at user-quota properties on servers. Although usernames appear for existing users, only the SID shows for deleted users.
- When you're looking at Recycler Bins on remote servers. In this case, you’ll never see a user ID or username, even for existing users. Only SIDs are given.
By maintaining a history of SIDs, you can easily determine who a SID once belonged to later on, after the user or group has been deleted. This information can come in handy if you're being audited and an auditor asks why certain permissions on sensitive files only list a SID. The auditor will likely want to know who that SID once belonged to. If a server's C drive is running low on space and you see that someone is taking up megabytes of space in the Recycler Bin, you can use the SID database to quickly find out who that person is.
To make it easy to search the database for SIDs, I created an HTML Application (HTA) named SIDFinderLite.hta. In this article, I discuss how to use UpdateSIDdb.vbs and how this script works. In Part 2, I'll discuss how to use SIDFinderLite.hta and how that HTA works.
The SID database isn't complicated. It has three fields for each user or group object in a domain: User, SID, and SIDType. The User field stores the object's name, which is usually a user's or group's ID. The SID field holds the string of unique alphanumeric characters used to identify that object. The SIDType field contains a numeric value that represents the type of object. As Table 1 shows, there are several types, but you'll likely see the first five types only. With the User, SID, and SIDType fields, you'll have all the information that you need to ascertain which user or group a particular SID currently belongs to or used to belong to.
To create the SID database, you can use the UpdateSIDdb.vbs script. You need to run this script on one of your domain controllers (DCs), preferably the closest one to you. By using a DC as your master data source, you can easily acquire all your domain user and group SIDs from one source. Alternatively, you can run this script on other computers if you want to keep individual databases of SIDs on specific servers.
Let’s take a look at UpdateSIDdb.vbs' main block of code, which Listing 1 shows. This code sets up several variables and calls three subroutines. In this code, you need to modify the line in callout B by replacing DomainControllerName with the name of your closest DC. What you specify in this variable will represent the master data source of your SID database. If you want the script to use the local machine rather than the DC as the data source, you need to comment out the line in callout B and uncomment the line in callout A.
Besides specifying the data source, the code in Listing 1 also specifies the database's name and where that database will reside through the FileString variable. As currently set up, the database's name follows the format: DRS-computer name-SID.xml. (DRS is short for Disconnected Recordset.) For example, if a DC named MyDomainDC1 is your master data source, a database named DRS-MyDomainDC1-SID.xml will reside in your C:\Temp folder after the script runs. If you don't want the database to reside in your C:\Temp folder or you want to change the database's name, you can modify the FileString variable's value. After assigning the value to the FileString variable, the OpenUpdateXML subroutine is called into action.
The OpenUpdateXML Subroutine
The OpenUpdateXML subroutine, which Listing 2 shows, creates the database if it doesn't exist, opens the database, and updates the database. Listing 2 shows this subroutine. Near the beginning of it, I create an instance of a disconnected recordset with the code
Set DRS = CreateObject("ADOR.Recordset")
Next, I check to see whether the SID database already exists. If not, I call the CreateDRS subroutine to create the database. (I’ll cover that code shortly.) Immediately after the call to CreateDRS, I open my database by using the ADOR.Recordset object's Open method with the name the database, which is stored in the FileString variable. Once the database is opened, it's a disconnected recordset, meaning that it has no ties whatsoever to the XML file just opened. At this point, the database exists only in memory.
After opening the database as a disconnected recordset, I call the UpdateSID subroutine to add any new user or group SID information that has been added to the domain but doesn’t yet exist in the database. Before I elaborate on the UpdateSID subroutine, though, I need to explain what takes place in the CreateDRS subroutine.
The CreateDRS Subroutine
Listing 3 shows the CreateDRS subroutine, which creates the database if it doesn't already exist. Notice that in the code at callout A, I declare the adFldIsNullable constant. You use this constant when defining fields in a database. By specifying this constant, you're indicating that the field can accept null values. In this script, the field values that I gather with Windows Management Instrumentation's (WMI's) Win32_Account class will always contain data, so including this constant isn't really necessary. (Including it doesn't affect the script at all.) However, if you decide to create a database that includes a field that might or might not contain data, you need to declare this constant if you want the field to accept null values.
After declaring the adFldIsNullable constant and clearing the DRS variable, I again create a disconnected recordset so that I can create the User, SID, and SIDType database fields. I create them by using the Append method of the Fields collection object, which you access through the ADOR.Recordset object. The Append method's syntax is
Fields.Append Name, Type,<br> DefinedSize, Attribute, FieldValue
Let’s examine this syntax using the first Fields.Append statement in Listing 3, which is
DRS.Fields.Append "User", _<br> 201,256,adFldIsNullable
In this statement
- User is the Name of the field being created.
- 201 indicates that the field's data Type is Long String.
- 256 specifies that the field's DefinedSize is a variable-length size. (By definition, any field width greater than 255 for this type of field will be treated as variable length, meaning that the field can store more or fewer than 256 characters.)
- The field's Attribute is adFldIsNullable, which means it accepts null values.
- Because the field isn't being assigned a value at this time, the FieldValue is omitted.
Note that for this script, I hard-coded a value of 201 for the field's data type. However, in your code, you might want to define and use a constant such as LongStrType instead so that the code is more intuitive. If you want to do this, you’d include the line
Const LongStrType = 201
in callout A, then replace 201 with LongStrType in each Fields.Append statement. So, for example, the first statement would look like
DRS.Fields.Append "User", _<br> LongStrType,256,adFldIsNullable
For information about the possible data types for fields, go to the DataTypeEnum Web page at http://msdn2.microsoft.com/en-us/library/ms675318.aspx. For information about field widths, go to the Append Method (ADO) Web page at http://msdn2.microsoft.com/en-us/library/ms681564.aspx.
After creating the new fields, I open the new disconnected recordset with the Open method. At this point, I'm ready to start adding data to the fields. The SID-related data that I want to add is obtained with the WMI Win32_Account class. I simply connect to the master data source computer, which in this case is the nearest DC, and attempt to query that DC for Win32_Account data. If an error occurs while connecting to the DC, the CreateDRS subroutine uses the ADOR.Recordset object's Close method to close the disconnected recordset and Windows Script Host's (WSH's) WScript.Quit method to terminate the script.
As long as there isn't error, the subroutine continues by cycling through the Win32_Account collection using a For Each…Next statement. For each item in the collection, I add a new record to the recordset using the ADOR.Recordset object's AddNew method.
With a new record available, it's easy to add data to each of the three fields. To add data, you simply specify the name of the recordset and field to which you want to add a value, then simply use the = operator to assign a value to the field. For example, the statement
DRS("User") = objItem.Caption
assigns the value returned by Win32_Account's Caption property to the User field of the SID database. In this case, the Caption property will return an ID in the format DomainName\UserID. Similarly, the SID and SIDType fields are populated with the Win32_Account's SID property value and SIDType property value, respectively.
The final line of code in the For Each…Next loop uses the ADOR.Recordset object's Update method to write the new record to the recordset. This updating of records continues until the subroutine cycles through the entire collection of domain accounts.
The UpdateSID Subroutine
The UpdateSID subroutine, which Listing 4 shows, uses the same steps to add data to the recordset as the CreateDRS subroutine, except that UpdateSID first checks to see whether a record with the same SID already exists in the database. If it does, the subroutine goes to the next item in the Win32_Accounts collection. If the record doesn't exist, UpdateSID creates a new record with the new data.
Callout A in Listing 4 shows the code that determines whether there's an existing record. First, I use the ADOR.Recordset object's MoveFirst method to make sure I'm accessing the first record in the database. Then, I assign a search string to a variable named FindTxt. The search string must follow the format: DatabaseFieldName = Value. If the value is a string, it needs to be enclosed in single quotes. Because I'm searching the SID field for a SID, which is a string, the search string will look something like
SID = 'S-125-234345345'
I use the SID value of each Win32_Account item to construct a search string each time I cycle through the Win32_Account collection. After assigning this string to the FindTxt variable, I use the ADOR.Recordset object's Find method to quickly determine whether that particular SID already exists. To determine whether that SID was found, I check the ADOR.Recordset object's EOF (end of file) and BOF (beginning of file) properties. If the record pointer is at either of these points, it means the record wasn't found and I can safety add the new record. As with the CreateDRS subroutine, this process continues until all the items in the Win32_Account collection have been evaluated.
The SaveIt and CloseIt Subroutines
After the update process is complete, all that’s left to do is save and close the disconnected recordset. Saving the recordset requires that you first delete the original database .xml file because you can't save a disconnected recordset to a file that already exists. So, as the SaveIt subroutine in Listing 5 shows, I create a Scripting Runtime Library FileSystemObject object, then use its Delete method to delete the original XML file if it exists. Afterward, I declare the adPersistXML constant, assigning it a value of 1, which specifies an XML format. I use this constant when calling the ADOR.Recordset object's Save method:
To use the Save method, you specify the pathname to the recordset you want to save (which, in this case, is contained in the FileString variable). If you want the saved file in a format other than the default Advanced Data TableGram (ADTG) format, you specify the format. In this case, I specify an XML format. To find out the other formats you can use, go to the Save Method Web page at http://msdn2.microsoft.com/en-us/library/ms681501(VS.85).aspx.
The last step is to close the recordset. That’s done by simply using the ADOR.Recordset object's Close method, as Listing 6 shows.
Stay Tuned for Part 2
Maintaining a SID database can come in handy when you need to retrieve a user's or group's ID but you have only a SID. With UpdateSIDdb.vbs, creating this database is a snap. If you're contemplating creating a SID database, the sooner you do it, the better. Any users or groups that were deleted prior to the creation of the SID database will be lost and irretrievable.
In Part 2, which will be posted on May 7, I’ll show you how I created SIDFinderLite.hta, which you can use to review, search, and filter the SID database. You can download UpdateSIDdb.vbs and SIDFinderLite.hta by clicking the Download the Code Here button. If you want to use SIDFinderLite.hta before I explain it in detail in Part 2, you should be able to get it up and running with little effort. You just need to modify the strComputer variable's value so that it specifies your DC's name. In addition, if you modified the FileString variable's value in UpdateSIDdb.vbs, you need to modify that value in SIDFinderLite.hta.