September 3, 2004 at 4:54 pm
What permissions are necessary to create and administer Database Maintenance Plans ( and their associated jobs). This should include the ability to restore backups of data and transaction logs in the event of data corruption or loss and run queries against tables such as sysdbmaintplan_history in MSDB.
We tried adding me to the 'db_backupoperator' role in each database, plus the 'DATABASE CREATORS' server role for restores.
I re-registered my new login in Enterprise Manager, but the Database Maintenance Plan section under Management is not visible. Neither are jobs that should already be there under the SQL agent. Also can't see the logs under management nor query the sysdbmaintplan_history table.
Is there anything short of system administrator that will let me do my job here?
rp
September 4, 2004 at 12:42 pm
Since I'm not getting any responses here, let me rephrase. Do you have to be a system administrator to set up Database Maintenance Plans and query system tables such as sysdbmaintplan_history table?
thanks
September 6, 2004 at 2:04 am
Without blowing away my existing configurations, I'd have to guess here: but it seems logical to me that you would need to be a sysadmin to be able to setup, configure, delete Database Maintenance Plans and their associated jobs.
Personally I recommend hand scripting your automatic backup routines. Database Maintenance Plans are really just a less powerful GUI for some really simple T-SQL commands. Profiler will show you someof the under-the-hood commands that are done. But basically all the commands you need to backup and restore are:
BACKUP DATABASE
BACKUP LOG
Which require the db_backupoperator role.
RESTORE DATABASE
RESTORE LOG
require db_creator role.
The commands you need to schedule a backup job:
sp_add_job
sp_add_jobstep
sp_add_jobserver
sp_add_jobschedule
use public access.
If you want to do maintenance on the databases, such as reindexing, etc
DBCC DBREINDEX
DBCC DBREPAIR
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
DBCC UPDATEUSAGE
require db_ddladmin or sysadmin privilages.
Julian Kuiters
juliankuiters.id.au
September 9, 2004 at 1:53 pm
Adding a user to the Server level Database Creator role, plus db_backupoperator role on each database, plus the TargetServersRole in MSDB helped somewhat but still doesn't give the user full capabilities for creating/monitoring backups and restores.
And for those who want to use the EM Database Maintenance Plan functionality, at least as a starting point, those permissions don't make that visible.
Even without that, if you grant database creator the user can drop and rename databases -- not something to give a non-DBA anyway. Seems like Microsoft's intentions were that these duties were to be reserved for people on the DBA team.
Randy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply