Limit Access from GUI for Sysadmin "Operators"

pgrovepgrove Posts: 12
edited September 19, 2008 8:39AM in SQL Backup Previous Versions
I have several people that I would like to give access to red-gate SQL Backup that are not/nor can they be in the sysadmin role.

1. Operators - they are monitoring jobs, and sometimes may need to run a special backup.
2. Developers - they may need to make occasional copies of databases to refresh their test environments, or for troubleshootign.

Other than that, though, I don't want these users to be able to restore (or write) and data. Since some of these systems are 'In-Scope' SOX systems, i also have to ensure that these specific users don't have the ability to write data to these SQL servers for our audit.

Is there any method I can use to allow them to use the GUI console to do what they need, but not give them SysAdmin rights?
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Assign execute rights to the following SQL Backup extended stored procedures for users that only need to use the GUI for informational purposes:

    - sqbutility, sqbdata

    For users that need to perform backups, grant them execute rights to the sqlbackup extended stored procedure.

    Note that even if you grant a user rights to the sqlbackup extended stored procedure, they still require SQL Server backup rights to the databases that they want to back up, unless they have sysadmin rights.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • pgrovepgrove Posts: 12
    I applied the rights (as listed below) for my user account.

    When I connect to the SQL Backup console and register the server, it gives the following message after trying to refresh:

    The user credentials supplied do not represent a member of the sysadmin role. SQL Backup requires sysadmin privileges.

    I still don't want to give them sysadmin priveleges, Any ideas?

    TIA :)
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    I'm sorry, my mistake. I was thinking of the version 4 GUI.

    Would the version 4 GUI meet your informational and operational needs?

    In addition to sqbutility, you need to also grant rights to execute the sqbstatus extended stored procedure to operators.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • asentellasentell Posts: 42
    Why is the GUI limited so that only those with sysadmin privileges can use it? We have someone in our team who we want to get involved in backups, but I hesitate to open up our full production SQL Server instances to him as sysadmin. I'll do that if I have no other choice, but for large organizations like ours there should be a non-sysadmin user option.

    Thanks,
    Aaron
    · Share on Twitter
  • asentellasentell Posts: 42
    Why is the GUI limited so that only those with sysadmin privileges can use it? We have someone in our team who we want to get involved in backups, but I hesitate to open up our full production SQL Server instances to him as sysadmin. I'll do that if I have no other choice, but for large organizations like ours there should be a non-sysadmin user option.

    Thanks,
    Aaron
    · Share on Twitter
  • asentellasentell Posts: 42
    It's been 10 days since I posted. Does anybody at Red Gate have any comments on this?
    · Share on Twitter
  • Matthew FlattMatthew Flatt Posts: 255 Bronze 5
    Hi Aaron,

    The SQL Backup GUI explicitly checks sysadmin access for the logged in user/SQL Authentication provided when connecting to the server.

    There is no way around this if you want your user to use the GUI, you could let the user use SQL Backup via the Extended Stored Procedures by granting execute access as per Peter's posts above.

    A change to let non sysadmin users use the GUI will be considered for a future release.

    Thanks.
    Matthew Flatt
    Redgate Foundry
    · Share on Twitter
Sign In or Register to comment.