allow a NON-SysAdmin the rights to run job in cmdshell

[Michael G]

RFA_help_NON-SysAdmin_RunDOS_Jobs.txt

I am trying to allow a NON-SysAdmin the rights to run a job in the command shell. (I'm referring to a very select group of people, some developers, etc. They do not have SysAdmin priveleges, but need to do some things occasionally.)

I've looked at
xp_cmdshell
sp_start_job
xp_sqlagent_proxy_account

but the help on these items didn't help me much (or confused me more!)

this fails if they try to:
exec master..xp_cmdshell 'h:filesvend
eelftp.bat'
or try to run a DTS that does it

Any ideas?

Thanks
Michael

[Chris Thibodeaux]

If you create a stored procedure, with DBO being the owner, and pass in a parameter (which is the operating system command), using dynamic sql, non-admin will be able to execute the procedure. You might have to give them execute permission on the proc. I haven't used this approach in a while, so I don't remember.

- HTH -


------------
Michael G at 8/19/2002 5:39:31 PM

RFA_help_NON-SysAdmin_RunDOS_Jobs.txt

I am trying to allow a NON-SysAdmin the rights to run a job in the command shell. (I'm referring to a very select group of people, some developers, etc. They do not have SysAdmin priveleges, but need to do some things occasionally.)

I've looked at
xp_cmdshell
sp_start_job
xp_sqlagent_proxy_account

but the help on these items didn't help me much (or confused me more!)

this fails if they try to:
exec master..xp_cmdshell 'h:filesvend
eelftp.bat'
or try to run a DTS that does it

Any ideas?

Thanks
Michael

[Michael G]

That didn't work.
I got essentially the same error: "A problem occurred while attempting to logon as the Windows user 'SQLAgentCmdExec': A required privilege is not held by the client. The step failed."

It doesn't matter if the thing I want to run is a OS Batch file, a DTS, or, now, a procedure. The only wan I can run it is as SysAdmin. If the owner is anyone else, or the current login is not sysAdmin, it fails. And, that needs to be changed so other particular users can also run it.

Thanks for your help, though.



------------
Chris Thibodeaux at 8/19/2002 6:29:07 PM

If you create a stored procedure, with DBO being the owner, and pass in a parameter (which is the operating system command), using dynamic sql, non-admin will be able to execute the procedure. You might have to give them execute permission on the proc. I haven't used this approach in a while, so I don't remember.

- HTH -


------------
Michael G at 8/19/2002 5:39:31 PM

RFA_help_NON-SysAdmin_RunDOS_Jobs.txt

I am trying to allow a NON-SysAdmin the rights to run a job in the command shell. (I'm referring to a very select group of people, some developers, etc. They do not have SysAdmin priveleges, but need to do some things occasionally.)

I've looked at
xp_cmdshell
sp_start_job
xp_sqlagent_proxy_account

but the help on these items didn't help me much (or confused me more!)

this fails if they try to:
exec master..xp_cmdshell 'h:filesvend
eelftp.bat'
or try to run a DTS that does it

Any ideas?

Thanks
Michael

[Alex]

The way I do with DTS

1. create DTS
DTS name = TEST_RUN_FOR_ALL

2. create stored procedure
ALTER procedure dbo.EXEC_JOB_TEST_RUN_FOR_ALL
as
exec master..xp_cmdshell 'dtsrun /S MY_SQLSERVER1 /E /N TEST_RUN_FOR_ALL'
GO

3.IMPORTANT !!!
In SQL server agent properties -->job system uncheck option "Only users
with Sys admin privelegies can execute CMDexec and Active scripting"

4.Stop service and start service.









------------
Michael G at 8/19/2002 6:45:07 PM

That didn't work.
I got essentially the same error: "A problem occurred while attempting to logon as the Windows user 'SQLAgentCmdExec': A required privilege is not held by the client. The step failed."

It doesn't matter if the thing I want to run is a OS Batch file, a DTS, or, now, a procedure. The only wan I can run it is as SysAdmin. If the owner is anyone else, or the current login is not sysAdmin, it fails. And, that needs to be changed so other particular users can also run it.

Thanks for your help, though.



------------
Chris Thibodeaux at 8/19/2002 6:29:07 PM

If you create a stored procedure, with DBO being the owner, and pass in a parameter (which is the operating system command), using dynamic sql, non-admin will be able to execute the procedure. You might have to give them execute permission on the proc. I haven't used this approach in a while, so I don't remember.

- HTH -


------------
Michael G at 8/19/2002 5:39:31 PM

RFA_help_NON-SysAdmin_RunDOS_Jobs.txt

I am trying to allow a NON-SysAdmin the rights to run a job in the command shell. (I'm referring to a very select group of people, some developers, etc. They do not have SysAdmin priveleges, but need to do some things occasionally.)

I've looked at
xp_cmdshell
sp_start_job
xp_sqlagent_proxy_account

but the help on these items didn't help me much (or confused me more!)

this fails if they try to:
exec master..xp_cmdshell 'h:filesvend
eelftp.bat'
or try to run a DTS that does it

Any ideas?

Thanks
Michael