How to add RESTORE permission to Sql server 2000 & 2005 users

How to add RESTORE permission to Sql server 2000 & 2005 users. I need to
add only Restore permission to the existing user.
*** Sent via Developersdex http://www.codecomments.com ***
Mohammed,
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 if you
are satisfied with the other rights that they will also get.
Or (apparently) you can make one user the owner of a database and he should
then be able to do a restore of that database. (I have not tested the owner
of the database approach today.)
RLF
"Mohamed Kaleel" <compguyy@.gmail.com> wrote in message
news:e5j1rZoUIHA.1212@.TK2MSFTNGP05.phx.gbl...
>
> How to add RESTORE permission to Sql server 2000 & 2005 users. I need to
> add only Restore permission to the existing user.
> *** Sent via Developersdex http://www.codecomments.com ***
|||Hi Russell,
Thanks for reply, i tried this scenario,i am not able to restore the DB.
If i grant sysadmin privilages to the user then i can able to RESTORE
the DB.
Please suggest me.
Thanks,
Kaleel
*** Sent via Developersdex http://www.codecomments.com ***
|||Hi Russell,
Thanks for reply, i tried this scenario,i am not able to restore the DB.
If i grant sysadmin privilages to the user then i can able to RESTORE
the DB.
Please suggest me.
Thanks,
Kaleel
*** Sent via Developersdex http://www.codecomments.com ***
|||Mohammed,
Of course, sysadmin will work, but I created a login RLFTest that is not a
sysadmin for these tests.
Test 1: Granted RLFTest the "dbcreator" server role and the "public" role in
MyDatabase.
RESTORE DATABASE MyDatabase ... was successful.
Test 2: Revoked RLFTest from the "dbcreator" server role and the "public"
role in MyDatabase. Made RLFTest the owner of the database.
USE MyDatabase
exec sp_changedbowner 'RLFTest'
RESTORE DATABASE MyDatabase ... was successful.
So, for me both of the approved non-sysadmin routes worked just fine. You
might retest using the details of what I did. If you still are having
problems, please let me know the details of error messages, etc.
RLF
"Mohamed Kaleel" <Mohamed.Kaleel@.kaleel.com> wrote in message
news:%231HKhE1UIHA.5164@.TK2MSFTNGP03.phx.gbl...
> Hi Russell,
> Thanks for reply, i tried this scenario,i am not able to restore the DB.
> If i grant sysadmin privilages to the user then i can able to RESTORE
> the DB.
> Please suggest me.
> Thanks,
> Kaleel
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||For the first Scenario, i get the following error
===================================
Restore failed for Server 'MyMachine\HTMS_PROD'.
(Microsoft.SqlServer.Express.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.0
0.3042.00&EvtSrc=Microsoft.SqlServer.Management.Sm o.ExceptionTemplates.F
ailedOperationExceptionText&EvtID=Restore+Server&L inkId=20476
Program Location:
at Microsoft.SqlServer.Management.Smo.Restore.SqlRest ore(Server srv)
at
Microsoft.SqlServer.Management.SqlManagerUI.SqlRes toreDatabaseOptions.Ru
nRestore()
===================================
System.Data.SqlClient.SqlError: Server user 'TestHtms' is not a valid
user in database 'HTMS_Temp1'. (Microsoft.SqlServer.Express.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.0
0.3042.00&LinkId=20476
Program Location:
at
Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQueryWithM
essage(StringCollection queries, ServerMessageEventHandler
dbccMessageHandler, Boolean errorsAsMessages)
at
Microsoft.SqlServer.Management.Smo.BackupRestoreBa se.ExecuteSql(Server
server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Restore.SqlRest ore(Server srv)
*** Sent via Developersdex http://www.codecomments.com ***
|||Mohamed,
Did you grant 'TestHtms' access to the 'HTMS_Temp1' database?
You will notice in my first scenario that it was necessary to grant some
rights to the database, though in my case simply making 'RLFTest' a member
of the public role was enough.
A note: If the restored database did not already have RLFTest as a user,
RLFTest lost access to the database once the restore was complete.
I notice that you are using SQL Server 2005 Express. That is was I also
used to test.
RLF
"Mohamed Kaleel" <Mohamed.Kaleel@.kaleel.com> wrote in message
news:e50kH3DVIHA.4360@.TK2MSFTNGP06.phx.gbl...
> For the first Scenario, i get the following error
> ===================================
> Restore failed for Server 'MyMachine\HTMS_PROD'.
> (Microsoft.SqlServer.Express.Smo)
> --
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.0
> 0.3042.00&EvtSrc=Microsoft.SqlServer.Management.Sm o.ExceptionTemplates.F
> ailedOperationExceptionText&EvtID=Restore+Server&L inkId=20476
> --
> Program Location:
> at Microsoft.SqlServer.Management.Smo.Restore.SqlRest ore(Server srv)
> at
> Microsoft.SqlServer.Management.SqlManagerUI.SqlRes toreDatabaseOptions.Ru
> nRestore()
> ===================================
> System.Data.SqlClient.SqlError: Server user 'TestHtms' is not a valid
> user in database 'HTMS_Temp1'. (Microsoft.SqlServer.Express.Smo)
> --
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.0
> 0.3042.00&LinkId=20476
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQueryWithM
> essage(StringCollection queries, ServerMessageEventHandler
> dbccMessageHandler, Boolean errorsAsMessages)
> at
> Microsoft.SqlServer.Management.Smo.BackupRestoreBa se.ExecuteSql(Server
> server, StringCollection queries)
> at Microsoft.SqlServer.Management.Smo.Restore.SqlRest ore(Server srv)
>
>
> *** Sent via Developersdex http://www.codecomments.com ***