SQL Server Performance

Best way to allow restores to a shared DB

Discussion in 'SQL Server 2005 General DBA Questions' started by RJSC, Dec 19, 2010.

  1. RJSC New Member

    I have a SQL server that hosts a few databases and I have a user who needs full access to one of those database, including the ability to restore from a backup. The problem that I have is that when he performs the restore, his user account becomes orphaned and he can no longer access the database. I must then go in and restore his user account's privileges to that database. How can I prevent his user account from losing privileges after performing a restore?
  2. satya Moderator

    Welcome to the forums.
    This is a 2 fold problem, let us take one at a time.
    Referring the BOL here about the permissions to solve the problem on restore:
    If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).
    RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.
    The second issue of avoiding the orphaned users, you need to run SP_CHANGE_USERS_LOGIN procedure this is best documented in this http://msdn.microsoft.com/en-us/library/ms175475(v=SQL.90).aspx link.
    FYI

Share This Page