Dynamic queries are
SET @sql = N'SELECT COUNT(*) FROM AppUsers WHERE Username = ''' + @user + ''' AND Password = ''' + @pass + '''' EXEC(@sql)
If value of user variable is myusername'' OR 1=1 -- the following query will be executed:
SELECT COUNT(*) FROM AppUsers WHERE Username = 'myusername' OR 1=1 --' AND Password = ''
Comment at the end of value of variable @username will comment-out trailing part of the query and condition 1=1 will be evaluated. Application that checks it there at least one user returned by this query will return count greater than 0 and login will succeed.
Using this approach attacker can login into application even if he don't know valid username and password.