The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

User ID's and Access -> SQL Server migration

The company I work for currently has one Access database for each of our users. We also have a SQL Server database. We're trying to get everything out of the Access database and into the SQL Server database. We're currently using Classic ASP.

There's a lot of SQL code scattered across ASP's. It's mostly in T-SQL. I would prefer to not have to go through each file and change the code to use SQL Server.

I'm contemplating just leaving the schema as is for now and using SSMA to move the tables into SQL Server. The tables, however, would need to have User ID's, to separate records from different users. Before, they were separated just by being in different databases.

Unfortunately, there isn't any abstraction layer to which I can add the user ID to each SQL statement. The only thing that almost all queries have in common is that they use the same include file to initialize the connection object.

I'd like to somehow get SQL Server to know what the user's Real ID is, based on something that gets done at the time I create the connection object. Is there any way to associate variables with a connection, that can be accessed in a SQL Server function? Or could I create a wrapper ODBC driver for the connection that either provides a way for SQL server to get the user ID, or even puts it into every SQL statement? I know this might make it so connection pooling doesn't work.

Eventually, the current code base will be worked through. There are a couple of features that we want now that are pretty dependent upon getting moved over to SQL Server.
Ben Atkin Send private email
Thursday, November 02, 2006
You might be able to accomplish what you're looking to do through the user of object owners in SQL Server. Basically, you create each user's objects in SQL Server and specify that the given user owns the object.

For example, if you have two database users, Sally and John, and they each own a customers table, it would look something like this:


Whenever Sally issues a Select * from Customers, SQL Server looks to see if Sally owns an object names Customers, finds that she does, and returns the results from her table.

Similarly, whenever John issues the same command, SQL Server looks to his customers table and returns the results.

There's more to it than this obviously, but if you look in BOL for "owner of database object" you'll find more information.
Tim Send private email
Thursday, November 02, 2006
I think Tim's idea is pretty spot on, but how are the users actually authenticating to the sql server?

If they are all logins on the sql server Tim's idea will work, but if the ASP server is connecting to the sql server with one account you may be screwed on this.

BTW, what is SSMA?
D in PHX Send private email
Friday, November 03, 2006
Great point, D.

I've spent so much time developing thick apps that I forgot to consider whether they might be using a proxy account to connect to SQL Server.
Tim Send private email
Friday, November 03, 2006
if you need to actually access the name of the current login in a SQL statement you can use the suser_name() function.

select suser_name()

But you'll run into the same issue the above posters mention if you are using a proxy account for connecting.
TetonSig Send private email
Friday, November 03, 2006
SQL Server Migration Assistant (SSMA) is a set of GUI tools Microsoft has for migrating from various databases to SQL Server. Currently they have SSMA's for Access, Oracle, and Sybase. Other ones are coming out in the future. They're free add-ons to SQL Server, downloadable from Microsoft's website.

I downloaded the Access one. I definitely would need to script it in order for it to work with the project I'm on (as there are hundreds of Access databases out there), but I haven't found any documentation on how to script it yet.
Ben Atkin Send private email
Saturday, November 04, 2006
Tim & D in PHX,

The ASP currently just connects to one database account. I'm one of only three technical people at the company so I should be able to change that, though. And there is a single include file that sets the variable localConn to an Access connection object, so I should be able to re-code that to set localConn to a SQL Server connection object.

I think this might be the best approach to the problems I'm facing now. Thank you both very much for your answer to my question!
Ben Atkin Send private email
Saturday, November 04, 2006
Oops, I didn't realize there were three of you. Thank you all for your help!
Ben Atkin Send private email
Saturday, November 04, 2006
ben, came across your blog doing some research

checkout, I am the author of the technology described, which is a highly automated solution for access to sql server using the client server adp model.

Your article on Access User ID's and SQL Server, we have a solution for this as well. Works fine.
Stephen Koop Send private email
Tuesday, November 28, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz