Если закрыть прямой доступ к таблицам, а оставил доступ только к ХП, в которых динамически формируется строка запроса set @SQLString='select SomeField from SomeTable' и далее выполняется через вызов exec(@SQLString), то при попытке выполнить такую процедуру получишь сообщение об ошибке - попытка нарушения прав доступа. Дело в том, что текст запроса, находящийся в @SQLString выполняется не в контексте хранимолй процедуры, а в контексте пользователя, вызвавшего эту хранимую процедуру. То есть, для того, чтобы эта хранимая процедура нормально работала, нужно предоставить всем пользователям, ее вызывающим, права на прямой доступ к исходной таблице, а не только на запуск к хранимой процедуре.
А вот как такие штуки работают из роли приложения - честно говоря не знаю, не пробовал. Отзовитесь кто-нибудь из тех, кто пробовал. Не выпадает ли из контекста роли приложения динамически формируемый и выполняемый запрос?