locked
Taking ownership of a restored database RRS feed

  • Question

  • I am trying to develop an application that uses SQL Server as the database. The application uses a single user login to talk to the database.

    Since I do not own the server, I can get a single login with a password that I know. The login has dbcreator permissions, but nothing more. 

    With this, I can run CREATE DATABASE from my application just fine and the login will own the database and can do whatever it wants to do there. 

    The problem is with restoring a backup and I am having difficulties understanding SQL Server's behaviour here. I sometime have the need to restore the backup from another machine. If I do so, I seem to loose ownership and haven't found a way to get it back.

    After I run RESTORE DATABASE, it appears that I am not the owner. Whatever I do, I get a permission denied.

    I have been reading lots of posts and articles with various suggestions - including the famous autofix command. However, all of these seem to require priviledges that I do not have.

    I understand that the restored database's users have lost their association because the came from a different machine. What I do not understand is why I have ownership on a database that I create through CREATE DATABASE while creating a database through RESTORE DATABASE gives me something that I cannot use.

    Is there any way to perform the backup or the restore or something I can do after the restore so that I can use my own database again and does not require more advanced priviledges than the ones I have?

     

    Tuesday, January 31, 2012 2:33 PM

Answers

  • As a step in finding out what happens, can you check sys.database_principals, sys.server_principals and sys.databases when this happens?

    I would think that whoever does the RESTORE becomes the owner according to sys.databases (check the sid in sys.databases against sys.server_principals).

    If above holds, then check sys.database_principals. If that sid doesn't map to a valid sid in sys.server_principals, then I would assume that ALTER AUTHORIZATION would fix the owner issue.


    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by amber zhang Wednesday, February 8, 2012 8:06 AM
    Tuesday, January 31, 2012 5:00 PM
  • I experimented with this some time ago in answer to someone's question.  It is not just the rights to restore a database, but it also depends on the contents of the database.Here is a link to the post:

    http://www.sql-questions.com/microsoft/SQL-Server/35008366/restore-db-as-nonsa.aspx

    One sentence in the last paragraph needs an edit, I believe: ...  dbcreator can create NEW databases, but can only restore a database where it is already a user and still retain rights to that database.

    That was my best effort at the time, but see if it matches your experience

    RLF

    • Marked as answer by amber zhang Wednesday, February 8, 2012 8:06 AM
    Tuesday, January 31, 2012 9:19 PM
  • You create it as an SQL login on one server. Then you restore it on another server. Your SQL login own that database in master. However, in the database, dbo maps to a SID which does not exist on that server.

    The workaround is to change the owner of the database. If you don't have the permission, you will need to talk kindly to the DBA.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by amber zhang Wednesday, February 8, 2012 8:06 AM
    Tuesday, January 31, 2012 10:56 PM

All replies

  • As a step in finding out what happens, can you check sys.database_principals, sys.server_principals and sys.databases when this happens?

    I would think that whoever does the RESTORE becomes the owner according to sys.databases (check the sid in sys.databases against sys.server_principals).

    If above holds, then check sys.database_principals. If that sid doesn't map to a valid sid in sys.server_principals, then I would assume that ALTER AUTHORIZATION would fix the owner issue.


    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by amber zhang Wednesday, February 8, 2012 8:06 AM
    Tuesday, January 31, 2012 5:00 PM
  • I experimented with this some time ago in answer to someone's question.  It is not just the rights to restore a database, but it also depends on the contents of the database.Here is a link to the post:

    http://www.sql-questions.com/microsoft/SQL-Server/35008366/restore-db-as-nonsa.aspx

    One sentence in the last paragraph needs an edit, I believe: ...  dbcreator can create NEW databases, but can only restore a database where it is already a user and still retain rights to that database.

    That was my best effort at the time, but see if it matches your experience

    RLF

    • Marked as answer by amber zhang Wednesday, February 8, 2012 8:06 AM
    Tuesday, January 31, 2012 9:19 PM
  • You create it as an SQL login on one server. Then you restore it on another server. Your SQL login own that database in master. However, in the database, dbo maps to a SID which does not exist on that server.

    The workaround is to change the owner of the database. If you don't have the permission, you will need to talk kindly to the DBA.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by amber zhang Wednesday, February 8, 2012 8:06 AM
    Tuesday, January 31, 2012 10:56 PM
  • This issues still persists. Here is what I do

    • 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 not created on the same 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

    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. 

    Wednesday, February 15, 2012 12:31 PM
  • I am sorry that you are finding this so frustrating, but it is all a matter of how rights are handled. 

    Although your APPUSER "owns" the database at the server level, since it does not map to dbo inside the database (nor to any other login in that database) it has no rights internally.  Therefore, it cannot run any of the commands that reach into the database and change anything about the permissions there.

    If your database from another server were owned by your domain login [domain\you] then this would work since the SID of your domain login is owned by the domain and used on every SQL Server in your domain.  But APPUSER (a SQL Server login) in your case is really two distinct SQL Server specific logins, despite the similar names.   (For example: John Smith in Oshkosh is not the John Smith in Birmingham.) 

    It works just like it should. But the SA has the rights to make the identity change. 

    I believe that if your APPUSER login on all servers had the same SID then you should sneak through without a problem, much as described for the domain account.  This is doable, but it would mean involving someone with admin rights to make the changes on all the servers and databases where a login named APPUSER is used.

    RLF


    • Edited by SQLWork Wednesday, February 15, 2012 3:44 PM
    Wednesday, February 15, 2012 3:43 PM