Limit Access from GUI for Sysadmin \"Operators\"

Forum for users of Red Gate SQL Backup tool

Moderator: Chris Auckland

Limit Access from GUI for Sysadmin \"Operators\"

Postby pgrove » Fri Feb 01, 2008 11:52 pm

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?
pgrove
 
Posts: 8
Joined: Fri Feb 01, 2008 11:43 pm

Postby petey » Mon Feb 04, 2008 11:32 am

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 7
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm

Still not working.

Postby pgrove » Mon Feb 04, 2008 11:01 pm

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 :)
pgrove
 
Posts: 8
Joined: Fri Feb 01, 2008 11:43 pm

Postby petey » Tue Feb 05, 2008 9:59 am

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 7
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm

SQL Backup GUI for non -sysadmin

Postby asentell » Tue Sep 09, 2008 12:46 pm

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
 
Posts: 31
Joined: Tue Oct 03, 2006 3:44 pm

SQL Backup GUI for non-sysadmin

Postby asentell » Tue Sep 09, 2008 1:12 pm

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
 
Posts: 31
Joined: Tue Oct 03, 2006 3:44 pm

Anybody at Red Gate have any comments?

Postby asentell » Fri Sep 19, 2008 12:28 pm

It's been 10 days since I posted. Does anybody at Red Gate have any comments on this?
asentell
 
Posts: 31
Joined: Tue Oct 03, 2006 3:44 pm

Postby Matthew Flatt » Fri Sep 19, 2008 1:39 pm

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
Test Engineer, HyperBac
Matthew Flatt
 
Posts: 253
Joined: Tue Jan 15, 2008 4:45 pm


Return to SQL Backup Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests