Add user/instance to the group 'Microsoft SQL Server-Administrators'
This article applies to:
- Acronis Backup for VMware 9 (Acronis vmProtect 9)
- The virtual machine (VM) is running Windows Small Business Server;
- You are trying to create an Application-Aware backup of this VM;
- The attempt fails with the following error message:
Insufficient rights, Application aware backups are not possible, because the specified user is not a member of the group 'Microsoft SQL Server-Administrators'
Users that are members of the Windows Administrators group are not automatically granted permission to connect to MS SQL Server, and they are not automatically granted MS SQL Server administrator privileges. When they try to connect to MS SQL Server, a message is returned that states that the user/instance does not have rights to log in to MS SQL Server.
To add a user/instance to the group 'Microsoft SQL Server-Administrators' do the following:
- Download the make_sysadmin.zip script and unpack it.
- Run the script in the guest OS of the virtual machine running Microsoft SQL Server from command prompt:
A: Log into the machine under the user which you plan to use for application-aware backup processing.
B: Run command prompt "as administrator"
C: Issue 'cscript c:\temp\make_sysadmin.zip' command, where c:\temp\ is the folder where you have extracted the script to.
As the result the user who is logged into the system will be granted with Microsoft SQL administrator permissions for all SQL instances running on the machine.
(!) NOTE: all services which are working with these SQL instances must be stopped before running the script, since it switches the SQL instances into "single-user" mode which implies temporary stop of the instances.
1. Add a user/instance to the group 'Microsoft SQL Server-Administrators' using MS SQL Server Management Studio
A: Click the Start button, point to All Programs, click Microsoft SQL Server, right-click SQL Server Management Studio, and then click Run as administrator.
Note: The Run as administrator option elevates the user permissions.
B: Follow the steps in the below screen to add the permission level for sysadmin to the user/instance installation. Do this for every existing SQL Instance on the SQL-Server. You can check which instances are present using SQL-Server Configuration Manager.
2. In case you are unable to login via MS SQL Server Management Studio due to lack of permissions then you may need to start MS SQL Server in single-user mode
A: Open "SQL Server Configuration Manager" found in the Start Menu under Microsoft SQL Server -> Configuration Tools.
B: Right-click on the SQL instance you want to start in single-user mode and stop it.
C: Open properties of the SQL instance you want to start in single-user mode.
D (Optional / Maybe not required): Change the Logon Account to a different one:
E: Under the Advanced tab find Startup Parameters, add the -m; parameter (at the beginning). This parameter starts the instance in the single-user mode. In this mode every member of the local administrators group has sysadmin privileges on the instance.
Click Apply. Right-click on the SQL instance and start it. After that you will be able to add a user/instance to the group 'Microsoft SQL Server-Administrators' using MS SQL Server Management Studio as described in step 1 above.
Make sure to remove added parameter and restart instance to allow multiuser connection.
See also Acronis Backup for VMware 9.