main effect is that SQL can not perform any actions that require network connectivity, e.g. access shares on other servers, backup across the network, logshipping or mirroring would not work if you required those options.

Also local admin is more access at the the server level than SQL really requires so there is an enhanced security risk.