It's like most DBMS features, if you use it in the right situation it does it's job well, the wrong situation it does it poorly.
Some things just can't be done without it. Typically I have only found this to be for administrative work, and not application code. Some system commands don't allow for parameters to be used as input.
So for example if I need to run something through a sproc against every database, on many instances with unknown databases, and the command doesn't accept parameters, I usually solve this through dynamic SQL. This is more of a thing in Sybase ASE than MSSQL however.
An excellent application use in Sybase ASE is pivots without knowing the values to be pivoted. This can be done using dynamic SQL in a stored proc, but as far as I know Sybase ASE doesn't support the syntax to do this directly as a query. Only once the values are know can the query be written to pivot the data. - richardcrossley
I won't go much into it, since I think we all know it already, but there can be some risk to SQL injection if it's used incorrectly.
The larger one to me is that the query will be treated like what it is, a unique adhoc query, and not part of the compiled query plan. For something that runs occasionally, no big deal. For something that is executed hundreds of times a minute and that is going to have a lot of unique SQL, it would generate a lot of new, potentially unnecessary, query plans eating up cycles, and shortening the valid time of the plan cache.