Blocking MS Access from accessing a db

• micheal

  Valued Member

  Points: 72

  More actions

  August 19, 2002 at 3:11 pm

  #57865

  I have a SQL 200 database currently accessed through an application id by the applications. I am using SQL authentication.

  I have the need to log changes (before/after) to specific tables/columns and capture the NT logon of who made them.

  If i use NT authentication, users will have access to the db with Excel and Access. How can I lock out Excel/Access or how can I get the NT logon using SQL authentication?

• Andy Warren

  SSC Guru

  Points: 119902

  More actions

  August 19, 2002 at 3:33 pm

  #434816

  I've got a couple articles that discuss sql logins and authentication, one went live today if you're interested. If you're using a sql login you have to manage getting the NT login. You can determine it in the client app by something like Environ$("USERNAME") and then pass it to a proc (or all the procs). If you use NT authentication you can block users two ways, one is by using application roles, the other is to use app_name() and test everyplace to make sure its only your app (give it a weird name in the connect string for a little extra obscurity).

  Andy

  http://www.sqlservercentral.com/columnists/awarren/

  Andy
  Connect with me on LinkedIn