Post reply

USER DEFINED ROLE TO BACKUP ALL THE USER DATABASES ON THE SERVER

  • March 26, 2009 at 11:08 am

    #202769

    Is there a way I can create a user defined role which will have the privileges to backup all the user databases on the server?I do not want to create a login and grant it sysadmin.I just want the login to have privilages to backup all the databases.I know I can run a code like:

    EXEC sp_addrolemember N'db_backupoperator', N'LoginName'

    on each database to give it the privilages but i have more than 200 databases.Is there a way I can grant db_backupOperator privilages to the login on all the databases?

    thank you

  • March 26, 2009 at 12:16 pm

    #966935

    You could use undocumented sp_MSforeachdb.

    sp_MSforeachdb

    'use ?

    --exclude system databases

    if DB_ID(''?'') > 4

    EXEC sp_addrolemember N''db_backupoperator'', N''LoginName''

    '

    Greg

  • March 26, 2009 at 2:02 pm

    #967031

    Thank you Greg

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply