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…
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…
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.
ALTER AUTHORIZATION ON DATABASE::SalesDB TO RestoreUser;
Well… the restore of my database succeeded!
And if I try to restore another database it didn’t work.
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 😉