July 30, 2020 at 1:01 pm
We have a requirement for a Non Windows Admin & Non SQL Admin to be able to backup SQL databases to an external hard drive. It's a stand-alone server not on a network. There's a possibility of creating a higher lever SQL account with backup permissions. Then a script with encrypted password to invoke a backup job ?
The reason for this set up, is it has sensitive data, with only a few people able to access it. We want a power user to be able to attach an external drive, click 1 button to run backups on demand (not scheduled) to the external drive, then remove the external drive.
I think they prefer the user be able to run a script of some kind, rather than use SQL GUI to start a job.
July 30, 2020 at 2:02 pm
One way to do this would be to have a stored procedure with EXECUTE AS in it. This way, you can set the stored procedure to run as sysadmin then grant permissions on the stored procedure to whoever should be taking the backups.
That is the approach I would take as they get no elevated permissions (they don't need anything apart from public and execute on that one new stored procedure). I'd set the stored procedure to take 1 argument which would be the location of the backup file, and otherwise you control everything in there such as compression.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 30, 2020 at 2:07 pm
One way to do this would be to have a stored procedure with EXECUTE AS in it. This way, you can set the stored procedure to run as sysadmin then grant permissions on the stored procedure to whoever should be taking the backups.
In truth, the account the procedure runs under doesn't even need sysadmin privileges (and I would suggest it shouldn't). db_backupoperator is all it really needs to perform this task.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 30, 2020 at 2:18 pm
What app would you use to present the SP to the user so they can run it ?
July 30, 2020 at 2:21 pm
Thanks Thom! I didn't actually look into the permissions on this, was just going based on our setup. We have 3 DBA's all of who are sysadmins so just went with the first thing that came to mind. Having sysadmin and having a small DBA team means I often forget about those administrative permissions.
I agree that you shouldn't just use sysadmin because it is "easy"... same thing you shouldn't just grant "public" permissions on objects unless you are 100% certain that now and at any point in the future, public is acceptable. If that could change, then don't use public and you can save your future self the headache.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 30, 2020 at 2:54 pm
In truth, the account the procedure runs under doesn't even need
sysadminprivileges (and I would suggest it shouldn't).db_backupoperatoris all it really needs to perform this task.
Just as long as people trying to use this method realize that db_backupoperator is a database level role, so the setup would have to be done on each individual database that the user needs to backup.
July 30, 2020 at 3:56 pm
someone can run this in SSMS. Or make an agent job and use EXECUTE AS to call sp_Start_job
July 30, 2020 at 4:02 pm
They may want a BAT file or something to call the stored procedure.
July 30, 2020 at 7:33 pm
I would create a script in Powershell for the users - there are ways to prompt the users with a 'window' to get parameters but it is quite involved in setting up and creating the forms, however - a script that has required parameters will prompt them on the command line to enter the appropriate information.
You could also build a simple prompter and CLI menu system. The execute the script and it lists the options - they select the appropriate option and enter the destination and the script then uses Invoke-SqlCmd to execute the stored procedure.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 30, 2020 at 9:26 pm
It's vital to insure that these backups are securely encrypted. Highly sensitive data going to a removal drive would be a very bad practice otherwise.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply