1

I'm trying to restore a database from a backup into a different server using an user that is not sysadmin (with dbcreator server role).

According to this: http://technet.microsoft.com/en-us/library/ms190436(v=sql.105).aspx the user that restores the database is the dbOwner.

Using a sysadmin user I can see in the database properties that the user that restored the database is the owner, however after the restore I can't access the database.

I've already tried to add more server roles to the user (in the limit everything except sysadmin) and the result is always the same: I can restore the database but then I can't access it.

Is there any way of doing this without a sysadmin user?

Rob Baillie
  • 3,436
  • 2
  • 20
  • 34
nflash
  • 430
  • 3
  • 16
  • Any chance you ever found a way to do this? I just posted a similar question: http://stackoverflow.com/questions/21145524/database-not-accessible-after-restore – DanO Jan 15 '14 at 19:06
  • I haven't found a solution for this...We are requiring a sysadmin user to run our application that restores the database and then add permissions to the user that is used in the application that makes use of the database. – nflash Jan 31 '14 at 16:35
  • Thanks for the reply. After quite a bit of experimentation and research, I came to the same conclusion. If you are interested, there is more on the topic here - http://mattsql.wordpress.com/2013/01/10/restore-database-permissions (scroll down to the comments section) – DanO Feb 01 '14 at 02:31

0 Answers0