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 😉


Can you give me a list of all the users that can connect to our databases?

I often get the following question of my customers “Can you give me a list of all the users that can connect to our databases”. Actually, the customer wanted to have a list of users per database. To be complete, I added also the list of the Server Logins.

I created the following queries to answer that question.

First query will get all the server logins and the assigned server role(s). The logins that only have the public server role are also included in the list.

select as [Login], ISNULL(,”public”) as Serverrole
from sys.server_role_members srm
join sys.server_principals sp on sp.principal_id=srm.role_principal_id and sp.type=”R”
right outer join sys.server_principals sp2 on srm.member_principal_id=sp2.principal_id
where sp2.type in (”U”,’2014-03-28 19:35:36’G”,”S”) and sp2.is_disabled=0
order by 1

The second query will get all the database users for each database and the assigned database roles. The users that are not a member of any role are also included in the result set.

create table #DatabaseUsers (
[Database] sysname,
DBRole varchar(256),
DBUser varchar(256)

SET @CMD = N”use [?]
select db_name() as [Database],ISNULL(,””No database role assigned””) as database_role, as database_user
from sys.database_role_members drm
join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)
right outer join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)
join sys.database_permissions dp on mp.principal_id=dp.grantee_principal_id and dp.permission_name=””CONNECT””
where mp.type<>””R”””

Insert into #DatabaseUsers
exec sp_MSForEachDB @CMD

select * from #DatabaseUsers
drop table #DatabaseUsers

Remark: Disabled server logins and database users that are not allowed to connect to the database (REVOKE CONNECT) are not shown in the result set.

Have fun!