Limit Access from GUI for Sysadmin "Operators"

pgrove

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?

petey

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.

pgrove

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

petey

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.

asentell

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

asentell

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

asentell

It's been 10 days since I posted. Does anybody at Red Gate have any comments on this?

Matthew Flatt

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.