May 18, 2010 at 7:01 pm
We are currently developing a new database on our development server and for this we have granted dBO access to an empty database names space as dBO's
However, as we are making substantial changes to database I would like to take snapshots of the database. I have always done this by using backup and restore. However, our NEW environment just gives us straight DBO access which excludes these two roles. Turn around time for a backup or restore by placing a request to the sQL server support group is about 24 hours or I can do the task in 2 to 3 minutes if i have the access.
I believe it is possible to customize the roles in sQL 2005. can they be customized so that the DBO's can backup and restore their own database on a development server.
regards
Erick
May 19, 2010 at 4:09 am
Actually both the "database owner" fixed database role and the "dbo" user can back up the database, see
http://msdn.microsoft.com/en-us/library/ms189612.aspx
http://msdn.microsoft.com/en-us/library/aa905208%28SQL.80%29.aspx
Am I missing something?w
May 19, 2010 at 5:32 pm
The error is "Cannot access the specified path on the server" when trying to set the backup file location.
When attempting a restore the same error occurs.
There must be some additional Privileges to be able to save the backup file and also view the o/s folders which is not automatically granted to the dbo group. Anyone out there have any works arounds ?
regards
Erick
May 19, 2010 at 6:14 pm
ErickTreeTops (5/19/2010)
The error is "Cannot access the specified path on the server" when trying to set the backup file location.When attempting a restore the same error occurs.
There must be some additional Privileges to be able to save the backup file and also view the o/s folders which is not automatically granted to the dbo group. Anyone out there have any works arounds ?
regards
Erick
Backups are not actually run in the context of the user executing the backup. They are run in the context of the user running SQL Server. Verify that your service account has the necessary privileges to access the folders where you want these users to backup their databases.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 20, 2010 at 2:52 am
Jeff Williams is right, check the rights of the SQL Server service account on that location.
May 21, 2010 at 5:51 pm
it's a shared server. I login to our database as a dbo through windows authentication
although i can't backup and restore to the servers backup folder I can to c:\temp.
Not sure if this is always the case or that the server administrators can't lock this folder down
If it was the service account then i wouldn't be able to backup at all to the server. But i can both backup and restore by finding the file in the c:\temp folder
May 21, 2010 at 6:01 pm
If the user running SQL Server is the local system account, then that user has access to the folders on that server that it has been granted access to. Access to the temp folder is open to all users on a system.
If the user is a local user account - the same applies.
If the user is a domain user account - the above applies, and that user can be granted access to folders/shares on another system.
If you are trying to backup to a specific folder - then that user has to have been granted access to that folder - and this is your problem. The user running SQL Server does not have access to the folder you want to back up to.
This has nothing to do with SQL Server accounts - or who is running the command. It has everything to do with the user that has been setup to run SQL Server.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply