reject queries based on specific client_app_name and nt_username

Posted on

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.

Leave a Reply

Your email address will not be published. Required fields are marked *