March 12, 2009 at 1:21 pm
I've inherited a system in which 1/2 the users have some sort of sysadmin access and run stored procs with xp_cmdshell .... for instance:
CREATE PROCEDURE [dbo].[usp_RunDTS_xyz]
(@FileNumber int, @List_ID int)
AS
DECLARE @strRun varchar (255)
SET @strRun = 'DTSRun /S "SERVER_1" /N "CustLeads - CCP' + CONVERT(varchar, @FileNumber) + '" /A "gv_List_ID":"3"="' + CONVERT(varchar, @List_ID) + '" /E'
EXEC master.dbo.xp_cmdshell @strRun
I want to get away from this, but giving the users "normal" access means they can't run these stored procedures to invoke DTS packages.
What are some options to let them invoke the DTS packages without xp_cmdshell ?
March 12, 2009 at 1:59 pm
There is an option with XP_CMDSHELL with proxy account, check below for details.
http://msdn.microsoft.com/en-us/library/aa260700.aspx
Kind regards,
Gift Peddie
March 13, 2009 at 4:51 am
Gift Peddie (3/12/2009)
There is an option with XP_CMDSHELL with proxy account, check below for details.
Do run services on that proxy account.
March 13, 2009 at 4:57 am
Nikhil (3/13/2009)
Gift Peddie (3/12/2009)
There is an option with XP_CMDSHELL with proxy account, check below for details.Do run services on that proxy account.
Link given is script level...
You can do this by simple GUI.
expand management node on sql server 2000 enterprise manager,right click on sql server agent,
go to properties,go to job systems tab,uncheck that checkbox under Non-Sysadmin job step account.
This will give u pop up window to define proxy account.
after creating it and running agent under it you need to assign exec permission to that perticular user for executing XP_CMDSHELL
March 13, 2009 at 5:40 am
So I need a windows account with sysadmin rights to use as the proxy account ? Something like MYDOMAIN\SQL_Proxy, give it sysadmin permission,
then specify it in SQLAGENT properties Job System ? I'm on 2000 and going to 2005 soon.
March 13, 2009 at 7:18 am
That is correct because you cannot just remove these users without creating the proxy account because if you remove these users without proxy your DTS packages will fail. Check below for details, this option existed but only developers use it.
http://msdn.microsoft.com/en-us/library/aa260689.aspx
Kind regards,
Gift Peddie
March 13, 2009 at 9:37 am
I think I must be doing something wrong. Here's what I did:
Create a Windows Account called "MyDomain\SQL_Proxy"
Granted SQL sysadmin permission to "MyDomain\SQL_Proxy"
EXEC master.dbo.xp_sqlagent_proxy_account N'SET', N'MyDomain', N'SQL_Proxy', N'password'
EXEC msdb.dbo.sp_set_sqlagent_properties @sysadmin_only=0
Logged on as a non-sysadmin and tried to execute a command with xp_cmdshell but it failed with the usual:
EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo'.
Do I need to restart SQLServices ? (I hope not)
March 13, 2009 at 9:48 am
xp_sqlagent_proxy_account sets or retrieves the proxy account for the instance on which it is executed. The SQL Server service for that instance must be running under a Windows administrator account to read or set the SQL Server Agent proxy account.
Nope it may be bigger than restart because I got the above from the first link I posted read the remark and you will know all the default requirements.
Kind regards,
Gift Peddie
March 13, 2009 at 1:50 pm
Gift Peddie (3/13/2009)
xp_sqlagent_proxy_account sets or retrieves the proxy account for the instance on which it is executed.
The SQL Server service for that instance must be running under a Windows administrator account to read or set the SQL Server Agent proxy account.
Nope it may be bigger than restart because I got the above from the first link I posted read the remark and you will know all the default requirements.
But that goes against the best practice of having service accounts have minimal privileges , right ??
March 13, 2009 at 1:56 pm
In 2000 it is all you have but the relevant changes are made in 2005 and improved in 2008 so that account is not needed and you can just create an account to do just proxy account. In 2005 if you need to use the proxy it must go back to some one with that high permission who approved it.
Kind regards,
Gift Peddie
March 13, 2009 at 2:53 pm
Sorry, I don't quite get it yet. I have a Domain account I can use "Domain\SQL_Proxy" but in 2005, I'm not clear on how to make use of it for the users.
March 13, 2009 at 3:10 pm
In 2005 the execution context must be explict that is when you create the proxy for the Agent you also need to make sure the person can run SSIS package. Check below and see how use it and read the other related tips.
http://www.mssqltips.com/tip.asp?tip=1199
Kind regards,
Gift Peddie
March 13, 2009 at 10:25 pm
create one account on ur machine right click my computer go to manage create user give name SQL_proxy..
now add that user to administrator gr over there
now create same user in sql keep passwords same...
at sql also give it sysadmin rights...
this is d way to create service account
March 14, 2009 at 8:38 am
Nikhil (3/13/2009)
create one account on ur machine right click my computer go to manage create user give name SQL_proxy..now add that user to administrator gr over there
now create same user in sql keep passwords same...
at sql also give it sysadmin rights...
this is d way to create service account
administrator gr ?
But once the proxy account is set up in Windows, then added to SQL, where's the connection that let's users run xp_cmdshell ? Doesn't the user group have to get associated with the proxy account somewhere ?
(Sorry about my slow learning curve on this)
March 16, 2009 at 1:53 am
administrator gr ?
when u right click on my computer u will see manage there u can create user....right click on user u will find properties there add it to administrators group.
now you need to create acc in sql
give sysadmin right.
now run sql on this account
and then create proxy account as mentioned
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply