Introduction

After the change of the workstation, he started to install Microsoft SQL Server 2008 R2 on it and almost came across the traditional rake related to the improved security in this version. If Microsoft SQL Server 2005 included a group of local administrators in the sysadmin role on the SQL server by default, no one was included in this role in 2008:

 

As a result, the default installation has a situation where no one has administrative access to the instance, i.e. there is nothing to do with this instance but reboot it periodically. Also, such a situation occurs when the person who installed the SQL server, appointing himself as the sole administrator, leaves – for example, this situation has arisen in our admins.

This post shows the solution to this problem and provides an automated solution to this problem in the form of a script, just as tells the story of its writing, illustrating the power of WMI, which is inadmissibly hidden in the literature and on the Internet.

Description of the procedure

There is nothing unexpected or revolutionary in the solution:

• Reboot the instance to single user mode
• Add the desired user to the server administrators from under any user from the local administrator’s group
• Reboot instance to normal mode

 

Chewed up a description of the procedure

Overloading to single-user mode

• Start the SQL Server configuration snap-in and stop the required instance (in my case, the default instance)
• We’re opening up the properties of an instance
• Switch to the Advanced tab and scroll through the properties of the Startup Parameters parameter
• Add -m; (don’t forget the semicolon!). This parameter indicates that the instance is loaded in single user mode. In this mode, any member of the local Administrators group has system administrator privileges at the instance. Also in this mode, it is possible to have a single connection to the server, so any applications that may want to join the configurable instance must be disabled.
• We’re launching an instance.

 

Setting admin privileges for the user

There are many ways to do this, from connecting to the server via SQL Server Management Studio and using graphical snap-ins to adding the necessary permissions, to using osql. We will go the second way. Run cmd.exe under a user from the local administrator group and execute the command:

osql -E -S .\InstanceName -Q “EXEC sp_addsrvrolemember ‘DOM\User’, ‘sysadmin'”, where InstanceName is the name of the instance and DOM\User is домен\пользователь, which is given administrative access to the instance. In my case (with the default instance and for the admin user RU\venticello) it looks like this:

 

Launching an instance in the normal mode

We’re going in reverse order:

• Stop the instance
• Delete parameter -m;
• Start the instance

That’s it, that’s all!

Automation

Although the procedure is not archaic and not in any way every day, it is, to be honest, a little boring and tedious. One number of screenshots are proof of this. I am a convinced apologist of the statement that everything that is boring should be done by a computer and not by a human being – that is why they were created. Therefore I have taken and described all these steps in the form of the script offered to your attention. To use the script, it must be run from under a user with administrative privileges on the instance machine as follows:

cscript /nologo acquire_admin_rights.js [<instance-name>], where the optional parameter instance-name designates the instance to which the administrative rights for the starting user should be granted. If you skip the instance or specify the MSSQLSERVER name, access will be granted to the default instance. Let me remind you once again that we need to make sure that during the procedure there are no applications actively connecting to this instance, as they can intercept a single connection provided by a single-user mode.

 

In the process, the script is honest about its actions, so if something goes wrong, you can understand what the reason is and in what state the system is left behind:

Script details

When I started writing the script, I already had some experience with SQL Server configuration via WMI, but I did not have to work with the command line parameters of the instance launch. This is the key to my story: what I knew and how I was looking for what I needed.

WMI

In short, in the context of our narrative, WMI (Windows Management Instrumentation) is a Windows service that provides access to configuration information in a unified form of named classes, represented by a set of properties. The classes are scattered by namespaces (the most popular of which is root\cimv2, where most of the classes describing the system live, and root\default, where the registry class lives). Based on the class, there may be one or more instances of the actual objects described. For example, the Win32_Service class is a service concept, and each instance is a set of properties corresponding to the real services installed on the system.

 

Microsoft SQL Server in WMI

Here, as almost always with Microsoft, it didn’t cost much to be smoky. Although the SQL servers themselves provide backward compatibility, something has not worked out there at the configuration level, so that absolutely similar classes of configuration live in two different namespaces:

• root\Microsoft\SqlServer\ComputerManagement – for SQL Server 2005
• root\Microsoft\SqlServer\ComputerManagement10 – for SQL Server 2008

Correspondingly, in general, we have to look for our instance in two namespaces – but what if our script will want to configure the fifth server?

 

So, we know the namespace of the necessary classes, but what are they called and how to work with them? Here we get a rather nasty, but powerful utility – wbemtest.

wbemtest

wbemtest.exe is a standard WMI client (so standard that it is present in the paths) that it has been delivered with WMI since the first days of the service’s introduction in Windows 2000. As a consequence, the interface of this utility is rugged, which, however, does not diminish its power. It looks like this:

 

We don’t have much to do in this utility until we join the right namespace. Fortunately, we know the namespace: root\Microsoft\SqlServer\ComputerManagement10

 

If everything is okay with WMI (and this service tends to fall off occasionally) then the connection will be successful, inviting us to interact with the active buttons

 

All right, now we’re ready to dig into the namespace in search of the right classes and properties.

 

Searching for the right properties

First, let’s see what classes exist in this namespace at all. To do this, obviously, click on the Enum Classes button and click OK in the dialogue that appears not quite clear. As a result, the following window appears

 

The usual female intuition tells us that this is most likely the SqlServiceAdvancedProperty class. Click here to open the following dialogue box showing the properties of this class

 

Sounds like the truth. Let’s look at the instances of this class and see if there are any parameters we are interested in. To do this, click the Instances button and get this window:

Find the SqlServiceAdvancedProperty.PropertyIndex=13,SqlServiceType=1,PropertyName=’STARTUPPARAMETERS’,ServiceName=’MSSQLSERVER’. That’s the happiness!

 

Working with WMI from the script

Knowing what classes and properties we need, we can only access them from the script. We will consider JScript, because it is distributed with all Windows, and also JavaScript. Work on VBScript or PowerShell is similar. Work with WMI in the script begins as in the case of wbemtest with the connection to the desired namespace. This is done by the following code:

 

function LookupInstanceContext(instance, scope) {

try {

var wmi = GetObject(“WINMGMTS:\\\\.\\root\\Microsoft\\SqlServer\\” + scope);

var settings = new Enumerator(wmi.ExecQuery(“SELECT * FROM ServerSettings WHERE InstanceName='” + instance + “‘”));

if (!settings.atEnd()) {

return wmi;

}

}

catch (exception) {}

return null;

}

Depending on which version of SQL Server we are looking for, either “ComputerManagement” or “ComputerManagement10” is submitted as scope. Approximately the same code is used to connect to the root\cimv2 namespace, through which we work with services. The received object wmi implements the interface IWbemServices, but we are interested in three following methods:

• ExecQuery – execute a query in the WQL language and return the list of results
• Get – get a specific copy of the class by its identifier
• ExecMethod – call the method on the object

 

To practice the ability to make WQL queries and look at the results, our old friend wbemtest will help us by clicking the Query button… on the main window. In short, WQL (WMI Query Language) is a subset of SQL in which the name of the class is used as a table and you can not select specific columns – only SELECT *. For example, to find all instances of a server instance named MSSQLSERVER, you can write the following WQL query:

The result is presented in the same form as the class instances were returned to us (this was the result of the SELECT * FROM SqlServiceAdvancedProperty query).

 

To get one object by primary key or a full set of properties (for classes that do not have primary keys), the Get method is used. Here is the function responsible for getting the string value of the SqlServiceAdvancedProperty object along the specified path:

 

function GetPropertyValue(wmi, path) {

return wmi.Get(path).PropertyStrValue;

}

 

Changing the property value implies calling the SetStringValue method (which is specified in the description of the SqlServiceAdvancedProperty class). In order to call it, you need to create its argument to set the required value. This is done by the next batch of calls:

function SetPropertyValue(wmi, path, value) {

var arg = wmi.Get(path).Methods_(“SetStringValue”).inParameters.SpawnInstance_();

arg.Properties_.Item(“StrValue”) = value;

var result = wmi.ExecMethod(path, “SetStringValue”, arg);

if (result.ReturnValue != 0) {

throw new Error(“Failed to set property ‘” + path + “‘ to value ‘” + value + “‘”);

}

}

 

Conclusion

Otherwise, the script is self-documented. All actions are written in functions with clear names, suitable for reuse in their own scripts. Use it to your advantage!

 

In this post, the procedure of restoring administrative privileges on SQL Server has been considered, and also the power of scripting by means of WMI has been illustrated, allowing to automate all actions in the form of a small script. Let’s transfer manuals to scripts!