How do you grant a user xp_cmdshell, sp_OACreate, sp_OAmethod, and sp_Destroy on SQL server 2008? What is the prons and cons of granting this permission? Please let me know. Thank you!
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
All those 4 procedures require the sysadmin fixed server role or CONTROL SERVER permission. What are the pros and cons of granting this level of permission to a user that's not a DBA? This is like opening Pandora's box :-).
Instead you should consider using SQLCLR as some of the ones you listed are notorious for memory issues + security issues as well.
Note that you can also shoot yourself in the foot with SQLCLR but I am NOT qualified to speak abt it.