Maximum Failover Limit

With this blog post, I will try save a few hours of your life!

I’ve recently setup a simple 2 node failover cluster that will be used for AlwaysOn Availability Group. After the installation, I suggested the customer to perform failover testing, not only to see the behavior of the Windows Failover Cluster but also to see how the application(s) would respond to a failover. One of the tests was disabling the Production LAN.
When I disabled the NIC on the node that was hosting the cluster group, it did a nice failover as expected.

I activated the NIC again and did the same test on the other node (which was hosting the cluster group now). To my surprise, there was no failover at all but the cluster name and the IP address just went offline

This is not what I expected.
I checked the cluster events and saw the following error “Cluster role ‘Cluster Group’ has exceeded its failover threshold”

That’s right…, I forgot all about that. The maximum failover limit is set to 1 time in 6 hours.
I knew that you had to change this for testing. This is really good explained in this blog post .
The blog is using Windows Server 2008, but my installation is using Windows Server 2012 R2. It really took me a long time to figure out how I could change the maximum failover limit of the cluster group in Windows Server 2012…

You have to right click on the cluster name and select the properties. Now if you look closely, there is a “link” in the first windows called “Manage Core Cluster Resource Group”.
I didn’t notice that link because most of the links are just to open the help files…

Go to the Failover tab, and there you will find the Maximum failures in the specified period. By default it is set to 1 in a period of 6 hours. This is not enough during failover cluster testing and will give you the failed/offline issue that I had.
Change it to a higher value, like 100 for example.

Don’t forget to set it back to the default value after you finish your tests

I did a few more tests and it was working fine now!

Well, I hope that I have saved a few hours of your life with this 😉

Limit RESTORE to a single database

A client came to me with the following question: “Is it possible to limit a login to restore only one specific database?”
Apparently, the application that he was trying to upgrade took a backup of the database before the upgrade. In case of failure, the upgrade procedure would automatically restore the backup.
Good idea to restrict that user but I don’t know by hart if that is possible….

Let’s figure it out!

I’ve created a test login “RestoreUser” that isn’t a member of any server role, except Public. The login is linked to a database user that is member of the db_owner role of my Sales database.

Looks like I’m not able to perform a restore of the database with the current user rights.


If we check the server roles, there might be an option to use the dbcreator role. However, if we look at the description…

Members of the dbcreator fixed server role can create, alter, drop, and restore any database.

There might be an issue as we don’t want the user to create or restore other databases.
My test results confirmed this, so dbcreator role … no option…

Custom Server role

The database was running on a SQL Server 2012. Maybe I can create a new server role to put a restriction on the restore and add my login into that group.
The only option that looked interesting was the CREATE and ALTER DATABASE option. No RESTORE database option could be selected and the other roles were not relevant.

When I tried the restore, it didn’t work either. And even if it should have worked, the description mentions “any” database so again the same issue as the dbcreator role…

DDL Trigger

Maybe I can capture the RESTORE command in a DDL Trigger? In the trigger, I could check the user and the database that is been restored.
I’ve checked the DDL Events that are possible and the RESTORE command is not a DDL Event…

DDL trigger…? No option…

Database Owner

If you check the RESTORE command on Books Online and look for the security part, you will see the following:

If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database

We already know that the sysadmin and dbcreator are not an option, but let’s try “the owner of the database” option.

To change the owner of the database, you can execute this command. Of course, change the name of the database and the user to your database name and user.


Well… the restore of my database succeeded!

And if I try to restore another database it didn’t work.

Mission accomplished!

Is it wrong to change the Database Owner?

The database owner gets full privileges on the database itself. So be careful if you’re changing the database owner, that the former database owner can still access the database.
In this post of Brent Ozar, there is a check that the database owner should be sa. But in fact, it’s not wrong to have another database owner than sa.
It’s even recommended to create a dedicated account with zero privileges as the database owner. Check this post of Andreas Wolter on SQL Server Database Ownership.

Now, to come back to my situation. It’s not wrong to have my RestoreUser as the database owner. The user had already full privileges because it was member of the db_owner role.


Changing the database owner will allow the user to restore the just that single database. Other restores and creation of new databases will not be possible.
There are a few restrictions that you have to keep in mind. The database must already exist. If it doesn’t you must have CREATE DATABASE permission to restore the database. As a consequence, the user will be able to create any other database as well.
This option will also not work if you don’t allow database ownership for that user. If that’s not possible, you’re out of options.

I don’t know if there are other possibilities to restrict the user to restore 1 single database. If you do know, just drop me a comment 😉