Click here to Skip to main content
13,202,155 members (52,594 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


13 bookmarked
Posted 16 May 2010

Restricting Access to Database by Application Name and/or Host Name in SQL Server

, 16 May 2010
Rate this:
Please Sign up or sign in to vote.
Restricting Access to Database by Application Name and/or Host Name in SQL Server

Have you ever thought of restricting access to the Database by Application Name or Host Name, that’s on top of the user credentials.

For example, you don’t want users to use SQL Query Analyser but instead they should use Microsoft Access or another 3rd party application, or you want that only a certain Workstation can only access your SQL Server so when the user connects to the database he will be kicked out. For any reason, you might have a solution. I was checking over the internet whether there is a SQL built in security property that I can use to handle this scenario, but to my luck there is none. Since I am not a DBA I then asked some of the DBAs I know but they don’t have an answer either. So I devised my own solution by using triggers.

There are 3 trigger types in SQL which are:

  • DML Triggers (Data Manipulation Language) – The trigger that can fire on UPDATE, INSERT, or DELETE.
  • DDL Triggers (Data Definition Language) – The trigger that fires on CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS statements.
  • Logon Triggers – The trigger that fires on Logon, this is what we will use. Thank God this was implemented on SQL 2005 SP2.

It's very simple. Here is a sample of the trigger I created to handle my scenario.

CREATE TRIGGER RestrictAccessPerApplication
(PROGRAM_NAME() = ‘Microsoft® Access’ AND HOST_NAME() = ‘WORKSTATION_01')

So what does the code do. Every time a user logs in, the Trigger Fires and this will happen on all database instances, I tried restricting it to per database but logon triggers are global, I even tried adding that to the condition (DB_NAME() = ‘MyTestDatabase’) which definitely would not work as when you logon there is no database instance yet. Now for the condition part, if it does satisfy both conditions a user on WorkStation_01 using Microsoft Access to run queries in your SQL Database, then he will be kicked out regardless of him having access to the server, that’s the Rollback part. He will see a message something similar to this one.

Now it's up to your imagination how you would want to extend this one but be very careful as this is a Login Trigger. If your account gets caught with the conditions then you are in deep sh!@!#$t! You can even not revoke access if you want but just start to log events when it happens (Insert something in a table).

For me, I even added a table of applications, hosts and super users like such:

ALTER TRIGGER RestrictAccessPerApplication on ALL SERVER
PROGRAM_NAME() IN (SELECT sApplicationName _
	from MyTestDatabase.dbo.AllowedApplications WHERE bIsEnabled = 1)
	from MyTestDatabase.dbo.AllowedHosts WHERE bIsEnabled = 1)
	from MyTestDatabase.dbo.OverrideUsers WHERE bIsEnabled = 1)

Here is how my table looks like:

This works for me really well, but be very careful again when you want to change the table structure. Do it with care as one error in the script the condition will always fire and you will always be kicked out.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Raymund Macaalay
Technical Lead
New Zealand New Zealand

You may also be interested in...

Comments and Discussions

SuggestionBackup master Pin
Jan Steyn31-Aug-11 4:27
memberJan Steyn31-Aug-11 4:27 
Before implementing this just make sure that you backup master. Then you can easily rollback if things go wrong...

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171020.1 | Last Updated 17 May 2010
Article Copyright 2010 by Raymund Macaalay
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid