Advise needed on Single user to multi-user database change

I am converting a system that is single user to multi-user. My problem is
that I am not familar with SQL Server 2000 and am not sure how to approach
this problem.
The biggest part of this problem is the reports. The reports (Crystal
Reports version 4.X) run from tables that are built in a VB application.
The Crystal Report report form is linked to an odbc connection. Some of the
tables are long running and run the risk of another person starting the same
report and corrupting the data in the table.
The report forms seem to be locked to a table name and accessed via the odbc
connection. I tried to change the table name, but the report stopped
working.
My question is, is there a way I can create a table with the same name, but
only be visible from the current user?Hi,
You can create tables using the user's name.
But this name should be a valid SQL User account that has the permission in
the Database and you need to connect to the SQL Server using that account
or have sufficient credentials to access the other users tables.
How do you connect (authenticate) the user to the SQL server?
How are you planning to connect (authenticate) the different users to the
SQL server?
Do you plan to use the "sa" account or create separate accounts for each
users?
If you are planning to create separate accounts for each user, then you can
definitely create separate tables for each user using
Create table [username].[tablename]
This table will be visible only to the user that has created it, rather,
accessible only with that particular username's credential
Example
Create table [myname].[testtb] (FirstCol int not null default 0)
select * from testtb - would return an error, stating invalid object name.
select * from [myname].testtb - would return the records in that table
So, as long as you are using separate users this will work safely. Each
user will be able to view and access their own table, without the fear of
messing up with other users data.
If you are planning not to use separate username then you can create
temporary tables.
You can create local and global temporary tables. Local temporary tables
are visible only in the current session; global temporary tables are
visible to all sessions.
Prefix local temporary table names with single number sign (#table_name),
and prefix global temporary table names with a double number sign
(##table_name).
SQL statements reference the temporary table using the value specified for
table_name in the CREATE TABLE statement:
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)
But, since you need to access this tables from Crystal report and that
would be a separate connection, the local temporary tables would not be
visible to the Crystal Report.
This method would work, except for the integration with interaction via a
different connection to the database. As long as you use the same
connection object that created the tables, this
method is gonna work.
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.