best way to proceed, running XP_CMDSHELL/xp_create_subdir without sysAdmin

by Racer SQL   Last Updated January 11, 2018 13:06 PM

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
                    Compression,
                    Name = @NomeBaseDadosSemColchetes,

                    --Media Set Options
                    Init, 
                    Skip, 
                    NoFormat, 

                    --Error Management Options
                    CheckSum,
                    Stop_On_Error,

                    --Monitoring Options
                    Stats = 10,

                    --Tape Options                    
                    NoRewind, 
                    NoUnload

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.



Related Questions



MSDTC Issue SQL Server 2008 R2

Updated August 23, 2016 08:04 AM


Have Subquery return more than 1 Value

Updated January 16, 2018 16:06 PM