Sql-server – Maintaining or Re-Gaining access after SQL Server database restore without SYSADMIN rights

permissionssql serversql-server-2008

I am struggling with maintaining or re-gaining access after restoring an SQL Server database (2008 R2, if that makes any difference). Here is what I am trying:

  • SQLServer login APPUSER is member of the dbcreator group, but not sysadmin
  • APPUSER can run CREATE DATABASE and will own the database – fine
  • SQLServer login APPUSER maps to the database's user APPUSER with dbowner permission if APPUSER runs RESTORE DATABASE using a backup that was created on a different server, then RESTORE DATABASE will complete fine, but APPUSER seems to loose all access to the database
  • I checked sys.databases/owner_sid of the restored database in
    question and it matches sys.server_principals/sid of APPUSER. This makes me believe that APPUSER owns the database
  • I understand that inside the database, user APPUSER is no longer linked to the current SQLServer login APPUSER because the sid points to a user that was created on the other server and does not match the local APPUSER's sid

What I am trying to find is a way to have APPUSER restore a database and either maintain ownership or re-gain ownership without the need to have sysadmin rights. What does NOT work:

  • EXEC sp_change_users_login 'Update_One' … – permission denied for APPUSER, works for SA
  • EXEC sp_change_users_login 'Auto_fix' … – permission denies for APPUSER, works for SA
  • ALTER AUTHORIZATION … – permission denies for APPUSER, works for SA

So what do I need to do so that my APPUSER can restore a database and use it, even if the DB was from another server without APPUSER having sysadmin rights?

From a permission management perspective, it should be sufficient to grant permission to a login so it can create and restore its own database (dbcreator) but not require sysadmin to restore if the database came from a different PC.

In general, after having restored the database as APPUSER, that login seems to have no access at all to the database. Any SELECT would fail as well.
This is surprising, since APPUSER has performed the restore and the content of sys.databases/owner_sid seems to indicate that APPUSER is indeed the owner. Granted, a different APPUSER (different sid) has originally created the database on another server, but this is what I am trying to fix. I have at least 3 different ways for fixing this with sysadmin permissions, but none through the APPUSER login.

Best Answer

to relink the login to the correct id you will have to run (I assume that the login is appuser, and the databas user appuser):

alter user appuser with login = appuser ;

From BOL:

To change the name of a user requires ALTER ANY USER on the database. To change the default schema requires ALTER permission on the user. Users can change only their own default schema. Requires CONTROL permission on the database to remap a user to a login

As you explained that you have the db_owner role on the database, it should work.

Related Question