Getting administrative privileges in Microsoft SQL Server

Introduction

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



As a result, in the default installation, there is a situation in which no one has administrative access to the instance, that is, you cannot do anything with this instance except periodically rebooting it. Also, this situation occurs when the one who installed the SQL server, having appointed himself as the only administrator, quits - for example, this situation arose as our administrators.

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

Procedure description

There is nothing unexpected or revolutionary about the decision:

1. Reload instance to single user mode
2. Add the desired user to server administrators from under any user in the local administrators group
3. Reload instance to normal mode

Chew description of the procedure

Single User Overload

1. Run the SQL configuration snap-in server and stop the necessary instance (in my case, the default instance):
   
   
2. Open instance properties:
   
   
3. Switch to the Advanced tab and scroll through the properties to the Startup Parameters parameter :
   
   
   
   
4. Add the -m parameter ; (do not forget the semicolon!). This parameter denotes the loading of the instance in single user mode. In this mode, any member of the local administrators group has system administrator privileges on the instance. Also in this mode, a single connection to the server is possible, so any applications that may want to join a configurable instance must be extinguished. A full description of the database engine parameters can be found here :
   
   
   
   
5. Start the instance:
   
   

Setting admin privileges for the user

There are many ways, ranging from joining a server through SQL Server Management Studio and using a graphical snap-in to add the necessary permissions and ending with using osql . We will go the second way. Run cmd.exe under a user from the local administrators group and execute the following command:

osql -E -S .\InstanceName -Q "EXEC sp_addsrvrolemember 'DOM\User', 'sysadmin'" , where InstanceName is the name of the instance, and DOM \ User is the domain \ user who is given administrative access to the instance. In my case (with the default instance for the RU \ venticello admin user) it looks like this:

Running an instance in normal mode

We go in the reverse order:

1. Stop the instance
2. Remove the -m parameter ;
3. We launch instans

That's all!

')

Automation

Although the procedure is not daunting and in no way everyday, it is, frankly, a bit boring and tedious. One number of screenshots is proof of that. I, however, am a staunch apologist for the assertion that everything that is boring should be done by a computer, and not by a man - that’s what they created. Therefore, I took and described all these steps in the form of a script offered to your attention. To use the script, you need to start it from under a user with administrative privileges on the machine with the instance as follows:

cscript /nologo acquire_admin_rights.js [<instance-name>] , where the optional parameter instance-name denotes the instance to which the admin rights must be granted to the launching user. If you skip the instance or set the name MSSQLSERVER , access will be granted to the instance by default. Once again I remind you that you need to make sure that during the procedure there are no applications actively connecting to this instance, since they can intercept the only connection provided by the single-user mode.

In the process, the script honestly talks about its actions, so if something goes wrong, you can understand the reason and the state of the system:

Details on the script

When I started writing the script, I already had some experience with SQL Server configuration via WMI, but it wasn’t necessary to work with the instance command line parameters. It is in this vein that I will lead the story: what I knew, and how I searched 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. Classes are sorted by namespaces (the most popular of which is root \ cimv2 , in which most classes that describe the system live, and root \ default , in which the registry class lives). Based on the class, there can be one or more instances denoting the actual described objects. For example, the Win32_Service class is a service concept, and each instance is a set of properties corresponding to real services installed on the system.

Microsoft SQL Server in WMI

Here, as is almost always the case with Microsoft, it didn’t do with curliness. Although the SQL servers themselves provide backward compatibility, something they didn’t grow together there at the configuration level, so absolutely similar configuration classes live in two different namespaces:

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

Accordingly, in the general case, we need to look for our instance in two namespaces - well, what if our script wants to configure the fifth server?

So, we know the namespace of the required classes, but how are they called, and how to work with them? Here we come to the rescue one rather clumsy, but powerful utility - wbemtest.

wbemtest

wbemtest.exe is a standard WMI client (so standard that it is present in the paths) that has been supplied with WMI since the first days of the appearance of this service already in Windows 2000. As a result, the interface of this utility is harsh, which, however, does not account for its power. It looks like this:



Until we join the desired namespace, we have nothing to do with this utility. Fortunately, we know the proper namespace: root\Microsoft\SqlServer\ComputerManagement10 :



If everything is fine with WMI (and this service has a tendency to fall off from time to time), then the connection will be successful, inviting us to interact with active buttons:



That's all, now we are ready to delve into the namespace in search of the necessary classes and properties.

Search for desired properties

At first we look, what classes generally exist in this namespace. To do this, obviously, click on the Enum Classes button and in the appeared not completely clear dialog, click OK. As a result, the following window appears:

.

Common female intuition tells us that this is most likely the SqlServiceAdvancedProperty class. We open the following dialog, showing the properties of this class:



It looks like the truth. Let's look at 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 object , SqlServiceType = 1, PropertyName = 'STARTUPPARAMETERS', ServiceName = 'MSSQLSERVER' . Here it is happiness!

Working with WMI from a script

Knowing what classes and properties we need, it remains only to access them from the script. We will consider JScript, because it is distributed with all Windows, and even JavaScript. Work on VBScript or PowerShell is similar. Working with WMI in a script begins as in the case of wbemtest by connecting to the desired namespace. This is done with 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; } 

The scope is either “ComputerManagement” or “ComputerManagement10”, depending on which version of SQL Server we are looking for. About the same code, we join the root \ cimv2 namespace , through which we work with services. The resulting wmi object implements the IWbemServices interface, but we are interested in the following three methods:

• ExecQuery - execute a query in WQL and return a list of results
• Get - get a specific instance of a class by id
• ExecMethod - call method on the object

To practice your ability to do 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 SQL subset in which the class name is used as a table and you cannot select specific columns - only SELECT * . For example, to find all instances of a server instance named MSSQLSERVER, you could write the following WQL query:



The result is presented in the same form in which we returned instances of the class (this was the result of the SELECT * FROM SqlServiceAdvancedProperty ).

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

 function GetPropertyValue(wmi, path) { return wmi.Get(path).PropertyStrValue; } 

Changing the value of a property involves calling the SetStringValue method (which is specified in the description of the SqlServiceAdvancedProperty class). To call it, you must first create its argument in which to set the required value. This is done with the following packet 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

The rest of the script is self-documenting. All actions are written to functions with clear names, suitable for reuse in their own scripts. Use on health!

In this post, we reviewed the procedure for restoring administrative privileges on SQL Server, and also illustrated the power of scripting with WMI, which allowed automating all actions in the form of a small script. Perekuju manuals scripts!