With a surge of applications that can be used to pull information, my sql server is constantly getting tapped, and there are a couple of users that keeps running refresh. Is there a way to reject query based on specific client_app_name and nt_username? Alternatively, is there a way to add the combination of the user and the app to security to decline access to SQL? i.e. Approve the user access if client_appname is excel but decline if the appname is ‘Mashup Engine’.
Yes, you can use a
Logon Trigger and call
ROLLBACK if you want to deny the logon.
Alternatively if you are on Enterprise Edition you could use these in a resource governor classifier function to route them to a workload group in a resource pool with limited resources.
app_name() is potentially easily settable from the client if they are making adhoc connections, e.g. via SSMS, or can edit the connection string used by your app.