After reading a lot about this issue, I know that if I create a proxy account, I can do what I want. I just need some enlightenment to see if there's a better way to do what I want.

We have a procedure ( a backup procedure ) inside a database, that create subfolders. So the user can backup the database, run his work, and then backup it again. So We have 2 simple backups. if there's some problems, they tell us, and we ( dbas ) restore the database with the "before" backup.

Well, this is the procedure ( without the SETS and DECLARES ):

    Exec xp_create_subdir @Diretorio

    Backup Database @NomeBaseDadosSemColchetes 
        To Disk = @NomeCompleto 
               With --Backup Set Options
                    Name = @NomeBaseDadosSemColchetes,

                    --Media Set Options

                    --Error Management Options

                    --Monitoring Options
                    Stats = 10,

                    --Tape Options                    

Problem 1) To run this, I need a login that is SYSADMIN, because of the xp_create_subdir.

I read that If a create a Proxy account, I can create a windows login, configure it to run without sysadmin, and then configure it to be a proxy account.

The only way to do this ( Proxy account ) is using a Windows account?

I notice this problem because I found out that developers are using this SysAdmin account to do stuff ( nothing wrong, but putting databases offline, dropping their databases without noticing me, and etc )

So, how can I drop the SysAdmin permission for that login ( it only uses sysadmin because of this xp_create_subdir. or is there a better way to do this?

And, the DEVS know the password for this login, because we need to pass it inside their software.

  • 1
    I would suggest instead of using all these subfolders, you keep their backups in a single place that they already have permission to write to, and use filenames to keep the backups straight. Like dbname_BEFORE_timestamp.bak and dbname_AFTER_timestamp.bak. Or you could consider using full recovery and then it should be easier to restore to a point in time - when something bad happens - than always maintaining two copies of full backups every time they do something. It would also be easier to automate long-term cleanup of this single folder, too. – Aaron Bertrand Jan 11 '18 at 15:28
  • I was so blinded trying to solve code issues, logical and etc I didn't think about it. – Racer SQL Jan 11 '18 at 19:29
  • While the best solution is already written above, please note that to be able to execute xp_cmdshell one should not by sysadmin. You can just map the login to master and give him the execute permission on xp_cmdshell. – sepupic Jan 12 '18 at 8:59
  • Another solution may be creating a procedure in master with EXECUTE AS user clause where the user is granted execute on xp_cmdshell. The only permission that final user should have in this case is EXECUTE on this sp – sepupic Jan 12 '18 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.