Logging off a SQL Server user from Access

  • Thread starter Thread starter JRE
  • Start date Start date
J

JRE

I'm building an Access 2000 application connected to a SQL Server 2000
database with DAO and ODBC. I'm trying to have the application control the
SQL Server authentication by logging in to the database as "AppNameAdmin"
and loading a user table. I then find the network username in the table and
get their SQL Server login ID ("AppNameRead" or "AppNameWrite"). Using the
matching SQL Server login, I then link the tables or refresh the table
links.

The problem is that SQL Server Enterprise Manager shows both logins (even
though I previously closed the recordset where I used the "AppNameAdmin"
login (which is read/write). Since the first login allows read/write
permissions (and is persistent) and the second login is "AppNameRead", data
in the tables can still be edited (ignoring the fact that the tables were
linked with "AppNameRead", which has read only permissions).

Is there a programatic way in Access to log off user "AppNameAdmin" after
the user validation and before logging on and relinking the tables with
"AppNameRead"? I've looked high and low... There's a SQL Server stored
procedure to kill a login...but you can't kill your own login.

Any help would be appreciated.

Joe
 
Well if you were using ADO instead of DAO, you could use a separate
Connection for each login. The first login will be automatically closed when
you close that Connection.
 
Thanks Ron,

I'll try your suggestion. I know that ADO has a Disconnect event, but I
wasn't sure if it would really close that SQL Server login.

Regards,
Joe
 
Is there a programatic way in Access to log off user "AppNameAdmin" after

No, but there are a number of workarounds, including use of ADO as already
suggested.

The DAO equivilant is to open, use, and close an ODBCdirect workspace
for the AppNameAdmin login:

dim dbe as dao.dbengine
dim ws as dao.workspace
dim db as dao.database
dim rs as dao.recordset

set dbe = new dao.dbengine
set ws = dbe.CreateWorkspace("","","",dao.dbuseodbc)
set db = ws.opendatabase(....)
set rs = db.OpenRecordset(....)
....
rs.close
db.close
ws.close
 
Thanks Dave,

That's similar to what I did. You're right, DAO did everything I wanted with
the data, except it physically can't close the SQL Server session. I'm off
to ADO...

Regards,
Joe
 
That's similar to what I did. You're right, DAO did everything I wanted
with

Similar, but not the same? Notice that I actually have an independent
dbengine object, which is closed at the end of session, and it uses
a new ODBCdirect workspace, which does not share connections with a
Jet workspace anyway.

(david)
 
True. I had not tried that. I went ahead and recoded my app to use ADO (as
suggested) for the initial login and user verification. It works perfectly
now. I'll remember and try your DAO suggestion as time provides, possibly on
an upcoming assignment.

Thanks for you help and suggestions,
Joe
 
Back
Top