Prequalifying table names with the user name in reusable components

[John]

Hi,

I have a data driven application which has some generalized components. So,
for reuse, I am building the components so they can be reused in other
projects ... it takes almost no extra effort, just a bit of planning.

Following the security convention of only giving the user as much access as
required, I always create at least 2 users for each app; owner & user. And
recently I've had a cool idea for handling multiple sites with one DB; you
make the table owner the app owner user instead of dbo. This is useful with
a webhost, I don't have to pay extra for multiple databases OR have some
funky naming convention. You can have a separate 'users' table for each app
without name collisions. I'm sure others have done this, but I was pretty
happy with my little epiphany.

However, I have run into the following glitch:
My reusable components need to have the table owner coded into every SQL
statement! Damn it! How is this going to be reusable if every app that
uses it needs to have the same user names? Right?

Now the options as I see them are :
1. Adding an extra method 'username' parameter everywhere my connection
passed in.
2. Add the user name to the web.config file and use it when building every
sql statement.
3. Use my components as the table owner user.
4. Require an updatable view with the user as the owner, for every table
used by the component. In other words, have app_owner.users (table) as well
as app_user.users (view), and reference it like 'select * from users'.

Oracle has something called a synonym, which would allow me to remove the
prequalifying user name from all references, but SQLServer doesn't seem to
have anything comparable.

Personally, I don't like any of the options that I immediately recognize.
I'm guessing any reusable component accessing data must have the same
problem.

Is there a commonly used way to get around this?

Or does anybody see an option which I am missing?

Thanks in advance.
--
Regards,
John MacIntyre
http://www.johnmacintyre.ca
Specializing in; Database, Web-Applications, and Windows Software

[Chris Dunaway]

Have you looked at Microsoft's Enterprise library? They use a series
of .config files that contain information about the sql connections,
including user, etc. These files are edited using a config app that is
provided.

Here is the link:

http://tinyurl.com/5ga86

[John]

"Chris Dunaway" <du******@gmail .com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

Have you looked at Microsoft's Enterprise library? They use a series
of .config files that contain information about the sql connections,
including user, etc. These files are edited using a config app that is
provided.

Here is the link:

http://tinyurl.com/5ga86

Thanks Chris,

I will look into that.

Regards,
John