Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
matt_calhoon
Posting Yak Master
235 Posts |
 Posted - 2004-08-05 : 03:05:25
|
| Hi there,Ive never been able to get this to work unless the user account has sysadmin permissions. Here is my scenario;a stored procedure executed from an .asp page (running under a SQL user called mysqlUser), executes a SQL server Agent job (which in turn executes a DTS package).I dont want the stored procedure to run under a sysadmin login..only the login (mysqlUser) for that particular database. If I set the scheduled job to be owned by the sql login (mysqlUser) that accesses the stored procedure, I cannot execute the job.my logs give me this error: Non-SysAdmins have been denied permission to run CmdExec job steps. The step failed.I can get this working if I add the mysqlUser to sysadmin role.Is there any way to allow non sysadmin users to execute dts packages? and if so what are the security ramifications? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-05 : 14:22:54
|
So are you using xp_cmdshell in the sproc to run dtsrun.exe to execute the DTS package? If so:quote:
xp_cmdshell operates synchronously. Control is not returned until the command shell command completes.When you grant execute permissions to users, the users can execute any operating-system command at the Microsoft Windows NT® command shell that the account running Microsoft SQL Server™ has the needed privileges to execute.By default, only members of the sysadmin fixed server role can execute this extended stored procedure. You may, however, grant other users permission to execute this stored procedure. When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.Note In earlier versions, a user who was granted execute permissions for xp_cmdshell ran the command in the context of the MSSQLServer service's user account. SQL Server could be configured (through a configuration option) so that users who did not have sa access to SQL Server could run xp_cmdshell in the context of the SQLExecutiveCmdExec Windows NT account. In SQL Server 7.0, the account is called SQLAgentCmdExec. Users who are not members of the sysadmin fixed server role now run commands in the context of this account without specifying a configuration change.PermissionsExecute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users. Important If you choose to use a Windows NT account that is not a member of the local administrator's group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell.
Tara |
 |
|
|
matt_calhoon
Posting Yak Master
235 Posts |
Posted - 2004-08-06 : 01:33:19
|
| um - not sure. the stored proc fires this:EXEC msdb..sp_start_job @job_name ='jobnamehere' |
|
|
|
|
|
|
|
|
executing DTS and scheduled jobs from stored proc