Re: Minimum permissions to restore a database
- From: "Russell Fields" <russellfields@xxxxxxxxxx>
- Date: Mon, 28 Apr 2008 10:51:11 -0400
Ian (or is it Ian Wood, by any chance?)
Test 1 below is probably revealing your problem, if you just look down to
it.
Both SQL Server 2000 and 2005 have the same basic comments on RESTORE
permissions. Look down toward the bottom of the Books Online article on
RESTORE. It says:
RESTORE permissions default to members of the sysadmin and dbcreator fixed
server roles and the owner (dbo) of the database ... members of the db_owner
fixed database role do not have RESTORE permissions.
If the dbo, not db_owner, seems confusing it is like this. One login owns
the database, it may be 'sa' or 'MyDomain\MyLogin'. That login maps to the
dbo user and should have rights to restore the database. Inside the
database, many users may be in the db_owner role, but since those are inside
the database to be restored, they do not get the RESTORE permission.
So, you can make logins members of the dbcreator fixed server role or Or you
can make one user the owner of a database and he should then be able to do a
restore of that database.
To test the consequences I created a login TestLogin that is not a sysadmin
for these tests. This first test case is probably revealing the problem
that you are facing.
TEST 1: Granted TestLogin the "dbcreator" server role and the "public" role
in MyDatabase. (It is necessary that TestLogin have _some_ rights inside
the database to be restored.)
RESTORE DATABASE MyDatabase ... was successful.
NOTE: If the restored database did not already have TestLogin as a user,
TestLogin lost access to the database once the restore was complete since
the sysusers table was restored with the contents from the backup. So,
TestLogin needs to exist in the source backup if this user is to retain
rights after the restore.
TEST 2: Revoked TestLogin from the "dbcreator" server role and the "public"
role in MyDatabase. Made TestLogin the owner of the database.
USE MyDatabase
exec sp_changedbowner 'TestLogin'
RESTORE DATABASE MyDatabase ... was successful.
In other words, dbcreator can create NEW databases, but can only restore a
database where it is already a user. Hope that this helps you decide what
to do.
RLF
"Doownai" <Doownai@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:93267B73-C1D1-4282-B771-7283A911E421@xxxxxxxxxxxxxxxx
Given two servers: DevServer and ProdServer
Each Server has an AppAdmin user.
AppAdmin is a member of dbcreator role on both servers.
So long as we are creating DBs from scratch, all is good.
The problem I face is: When AppAdmin restores a database created on
DevServer to ProdServer he cannot access it. I can connect as a sysadmin
user and execute sp_changedbowner but I am trying to do this with the
least
permissions possible.
I have been trying different combinations without success. What are the
least permissions required to allow a database to be restored to a server
and
become operational?
Kindest Regards and thanks.
Ian.
.
- References:
- Minimum permissions to restore a database
- From: Doownai
- Minimum permissions to restore a database
- Prev by Date: Minimum permissions to restore a database
- Next by Date: Re: Granting EXEC for read only SPs
- Previous by thread: Minimum permissions to restore a database
- Index(es):
Relevant Pages
|
|
