I need to clear the logic behind Proxy account in SQL server 2005/2008.
1.If i want to create a proxy account to run the xp_cmd shell system stored procidures ?
2.And if i want to run the job steps
what i understand here is:
1 .above we can create a SQL/Windos Login and we can map this login to master database. it will create a user in master database, and we can aisign this user in master database xp_cmd shell extended strored procidure access from the securibles node or by running the scripts, however if this login/user dont have sysadmin access he can not run the xp_cmdshell extended stored prcidure in sql server query analyzer to achieve this task we need to create a credential using the EXEC sp_xp_cmdshell_proxy_account 'Windows LOGIN NAME','Password'.
Here 'Windows LOGIN NAME' can be any windows account having least privilage(can not have syadmin privilage).
2:To Run job steps using proxy account is where we have to use gui(off course ready scripts also) but for that we need to create a cridential first if you are using gui:
a) give name of cridentaial
b) choose any windows account(haveing no sys admin access) and give the password for this windows account, make sure you are giving the correct password here.
so now credentail part is done
now go to proxy under sql server agent
right click on proxy> new proxy> give a name to proxy, in credential column coose the credentail you have created > check appropriate job susbsystem> in pricipal choose the sql/windows login(as created in step 1 above having no sysadmin access) and who want to run the job steps. now ok
we are done now.
Note : The windows account we are using to make credential must have required access to run the jobs using proxy account(it does not mean it have sysadmin access) in fact sql/ windows login which we having no sysadmin access are using this credentail to use this proxy accoun to run all the required jobs.
Please let us know if my understanding is coorect on this login and comment if some thing is wrong here.Read more...