Welcome to dbForumz.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

How to add RESTORE permission to Sql server 2000 & 2005 us..

  
   Database Forums (Home) -> Client RSS
Next:  branding issue  
Author Message
Mohamed Kaleel

External


Since: Jan 08, 2008
Posts: 1



(Msg. 1) Posted: Tue Jan 08, 2008 10:11 pm
Post subject: How to add RESTORE permission to Sql server 2000 & 2005 users
Archived from groups: microsoft>public>sqlserver>clients (more info?)
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.developersdex.com ***

 >> Stay informed about: How to add RESTORE permission to Sql server 2000 & 2005 us.. 
Back to top
Login to vote
Russell Fields

External


Since: Feb 21, 2007
Posts: 457



(Msg. 2) Posted: Wed Jan 09, 2008 11:44 am
Post subject: Re: How to add RESTORE permission to Sql server 2000 & 2005 users [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
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" wrote in message

>
>
> 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.developersdex.com ***

 >> Stay informed about: How to add RESTORE permission to Sql server 2000 & 2005 us.. 
Back to top
Login to vote
Mohamed Kaleel

External


Since: Jan 09, 2008
Posts: 3



(Msg. 3) Posted: Wed Jan 09, 2008 10:20 pm
Post subject: Re: How to add RESTORE permission to Sql server 2000 & 2005 users [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
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.developersdex.com ***
 >> Stay informed about: How to add RESTORE permission to Sql server 2000 & 2005 us.. 
Back to top
Login to vote
Mohamed Kaleel

External


Since: Jan 09, 2008
Posts: 3



(Msg. 4) Posted: Wed Jan 09, 2008 10:22 pm
Post subject: Re: How to add RESTORE permission to Sql server 2000 & 2005 users [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
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.developersdex.com ***
 >> Stay informed about: How to add RESTORE permission to Sql server 2000 & 2005 us.. 
Back to top
Login to vote
Russell Fields

External


Since: Feb 21, 2007
Posts: 457



(Msg. 5) Posted: Thu Jan 10, 2008 8:53 pm
Post subject: Re: How to add RESTORE permission to Sql server 2000 & 2005 users [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
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" wrote in message

> 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.developersdex.com ***
 >> Stay informed about: How to add RESTORE permission to Sql server 2000 & 2005 us.. 
Back to top
Login to vote
Mohamed Kaleel

External


Since: Jan 09, 2008
Posts: 3



(Msg. 6) Posted: Fri Jan 11, 2008 2:36 am
Post subject: Re: How to add RESTORE permission to Sql server 2000 & 2005 users [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
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.Smo.ExceptionTemplates.F
ailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
at
Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseOptions.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.ExecutionManager.ExecuteNonQueryWithM
essage(StringCollection queries, ServerMessageEventHandler
dbccMessageHandler, Boolean errorsAsMessages)
at
Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server
server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)




*** Sent via Developersdex http://www.developersdex.com ***
 >> Stay informed about: How to add RESTORE permission to Sql server 2000 & 2005 us.. 
Back to top
Login to vote
Russell Fields

External


Since: Feb 21, 2007
Posts: 457



(Msg. 7) Posted: Fri Jan 11, 2008 8:27 am
Post subject: Re: How to add RESTORE permission to Sql server 2000 & 2005 users [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
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" wrote in message

> 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.Smo.ExceptionTemplates.F
> ailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
>
> ------------------------------
> Program Location:
>
> at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
> at
> Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseOptions.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.ExecutionManager.ExecuteNonQueryWithM
> essage(StringCollection queries, ServerMessageEventHandler
> dbccMessageHandler, Boolean errorsAsMessages)
> at
> Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server
> server, StringCollection queries)
> at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
 >> Stay informed about: How to add RESTORE permission to Sql server 2000 & 2005 us.. 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
unable to access sql 2005 database - in middle of restore - Hi folks I was restoring a database in SQL Server 2005 express edition. Due to a malfunction, system restarted. Now when I try to access the database, the status shows as "Restoring Database". I have tried to restart SQL Server and PC, but ...

How to create a CLR DLL for Sql Server 2005? Is it just an.. - How to create a CLR DLL for Sql Server 2005? Is it just any .net class library that you can load into Sql Server 2005 or is there some special project type that I need to start with?

sql server 2000 restore - For a small company, I need to come up with backup and restore procedures for a sql server 2000 database. This company will not consider using sql server 2005 until the end of 2006. Thus I am wondering if you can give me a script and/or point to me t...

How to install SQLserver 2005 in windowXP with SQLserver 2.. - Hello: I have a trouble on installing sqlserver 2005 on window xp which has sqlserver 2000 client. I would like sqlserver 2000 client and sqlserver 2005 Management Studio coexit together. However, I always got the error: Installatioin of sql server..

SQL Server 2005 Login Problem - We have recently purchased Small Business Server Premium, with SQL Server 2005 workgroup edition included. I has 3 XP PCs that I want to access the SQL Server database, and have set it up with windows integrated security. When I try to connect (from a...
   Database Forums (Home) -> Client All times are: Pacific Time (US & Canada)
Page 1 of 1

 
 You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]