>Will the effect then be limited to that SP? In other words if someone else is hitting the DB at the same time doing something else - they won't be affected by the 1st SP's setting?

When you execute the SP, it will implicitly change ANSI_NULL to OFF and then restore it after the SP executes. You should be able to prove this to yourself by creating two stored procedures, one with ANSI_NULL OFF and the other ON.

>> ... if someone else is hitting the DB at the same time doing something else

All SETs are scoped to the connection.

-Mike