Question :
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’.
Answer :
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.
Note the 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.