4

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.

0

2 Answers 2

1

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.

2
  • Running the above statement does not seem to work. SQLServer returns Cannot alter the user 'aurelio-lab', because it does not exist or you do not have permission Feb 15, 2012 at 14:34
  • 1
    You receive that error because you cannot alter the login of an account you are logged in as. You would have to be connected through a different account that has permission to run that statement.
    – user507
    Feb 20, 2012 at 11:05
1
exec sp_change_users_login 'Auto_fix', 'appuser'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.