Sql-server – Can a user with readonly access change SQL server settings

I wouldn't recommend messing with the system stored procs. You could wrap the sp_setapprole call in a stored proc and have your app call that proc instead. The wrapper proc would have the password hard-coded and use EXECUTE AS to impersonate a user who can use sp_setapprole. Then you could limit access to the wrapper proc. Another advantage to this approach is that the password doesn't go out across the network, nor is it included in your .exe file.

EDIT:

My mistake, you can't wrap the call directly. But you can wrap a modified version of the T-SQL that it runs. Looking at the version in my 2008r2 server, I see that the proc calls SETUSER intead of EXECUTE AS. Sad to see deprecated code in system stored procs. For yours, you may want to use EXECUTE AS instead.

You could also grant rights on whatever your app needs to function to a SQL user who can't login, give certain users rights to impersonate that user, and have your app EXECUTE AS the login-less user. This route still requires cookie handling to set the context back. In fact, it's very similar to a customized version of sp_setapprole, since both require some sort of impersonation.

Or, you could also create a database role and grant the role to authorized users. This seems like it might be more like what you want. Application roles are designed for app-defined rights. It seems like you really want user-defined rights.

There is no way to enforce a global setting or prevent people running

SET ANSI_NULLS OFF
SET ANSI_PADDING OFF

You can get a quick idea if your application is using any deprecated features by running

SELECT *
FROM   sys.dm_os_performance_counters
WHERE  object_name LIKE '%Deprecated%'
       AND cntr_value > 0
ORDER  BY cntr_value DESC 

If you do find areas of concern then you could set up a Server Side trace capturing the deprecation events.

This also returns the session id and SQL Handle of the offending batch to allow you to investigate further if you do see any events.

A slightly more sophisticated approach would be to set up a notification procedure to retrieve the text associated with the SQL Handle more quickly to reduce the probability that the offending SQL is no longer in the cache when you come to analyse the results.

Or you could probably use extended events to retrieve the TSQL along with the event.