none
Database owner after a restore

    Question

  • I recently discovered what looks to me like a bug in SQL server.  After some detailed testing, I've confirmed that this happens in SQL 2005 & SQL 2008 R2.  When a database is restored, the owner (looking into sys.databases (owner_sid)) is the login that ran the restore.  The issue that I've run accross is that, while sys.databases shows this login as the owner, if I pull it from the sysadmin group, it does not have access to the database as the dbo user is still set to sa (which was the owner prior to the restore).  Essentially, it appears that the restore adds the current user as owner in sys.databases but does not actually change the owner in the database.  To double-check this, I looked at the owner listed if I right click on the database and go into properties in SSMS (currently using 2012).  If I go into the security on the database and look at the dbo user, it's showing that it links to the login 'sa' instead of the "current owner."  If I change the owner of the database to something else and then change it back to the login that previously "owned" it after the restore, dbo is updated accordingly and the login now has access.  Is this a bug?

    Test criteria:

    1. backup a small database owned by sa.
    2. Restore the same database in a session using a login other than sa (this login obviously will need rights to restore).  (You can do this in a job or use EXECUTE AS).
    3. Ensure that the login used in step 3 is not sysadmin
    4. The login will now be listed as the owner in sys.databases, but will not have access to the database.
    5. Update the owner to some other login and then change it back.
    6. The login will be listed as the owner in sys.databases as before, but now dbo is linked to this login and the login will have the access as expected.

    Friday, August 02, 2013 2:29 PM

Answers

  • But the previous owner might not have a login on the new SQL Server where the database is being restored, or the login name and sid of the old owner might be a different person entirely on the new SQL Server. So maintaining the old owner information for sys.databases is not an option.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Friday, August 02, 2013 5:39 PM
  • I think you'll find there's a difference between the login that's assigned to the dbo user within a database, and the login that is listed as the owner of the database.

    The former relates purely in the context of the database itself, while the latter relates to the entire SQL instance.

    When you restore the database you're effectively creating it, and therefore the user that did so is the one that gets listed as the owner within the database properties. Since the sa login was previously mapped to the dbo user in that database, and the sa login is always there, the question of the user becoming orphaned becomes moot.

    The differences aren't always obvious, and I struggle to get them clear in my head to be honest, but there's a good blog here http://blogs.msdn.com/b/lcris/archive/2007/03/23/basic-sql-server-security-concepts-logins-users-and-principals.aspx that discusses the differences, purposes and how it all works together.

     
    Saturday, August 03, 2013 2:28 PM

All replies

  • I think you are saying that restoring a database does not automatically add the restorer as a database user. Which would be useful when the person who does restores is not a sysadmin and shouldn't have access to the database or its data. Database users are maintained inside the database. The restore process doesn't open up the database to adjust things like that.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Friday, August 02, 2013 3:55 PM
  • That makes sense to an extent, but then why show the restoring user as the owner in sys.databases?  Did Microsoft forget to add a step after the restore to see who actually has the owner role in the database?
    Friday, August 02, 2013 3:57 PM
  • But the previous owner might not have a login on the new SQL Server where the database is being restored, or the login name and sid of the old owner might be a different person entirely on the new SQL Server. So maintaining the old owner information for sys.databases is not an option.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Friday, August 02, 2013 5:39 PM
  • Definitely a good point, but isn't that the whole idea of orphaned users?  The other database users can be orphaned...why not dbo?
    Saturday, August 03, 2013 2:48 AM
  • I think you'll find there's a difference between the login that's assigned to the dbo user within a database, and the login that is listed as the owner of the database.

    The former relates purely in the context of the database itself, while the latter relates to the entire SQL instance.

    When you restore the database you're effectively creating it, and therefore the user that did so is the one that gets listed as the owner within the database properties. Since the sa login was previously mapped to the dbo user in that database, and the sa login is always there, the question of the user becoming orphaned becomes moot.

    The differences aren't always obvious, and I struggle to get them clear in my head to be honest, but there's a good blog here http://blogs.msdn.com/b/lcris/archive/2007/03/23/basic-sql-server-security-concepts-logins-users-and-principals.aspx that discusses the differences, purposes and how it all works together.

     
    Saturday, August 03, 2013 2:28 PM