3 database's into 1 - how to limit what users can see or access?

  • Thread starter Thread starter Rachel
  • Start date Start date
R

Rachel

Hi,
I have three databases (3 different locations) and I need to
emalgamate them into one db, with adp front end and sql server 2000
backend. My problem is that i will still have three different sets of
users using the new db but they do not want to be able to see the
others data. Also when reports are run they only want to see their
data in the reports not the other 2 locations. How could I do this
securely? Would it also be possible to have like an admin user who
could view all data not just individual locations?

Many thanks in advance

Rachel
 
Well, if you want to have three sets of data totally separate, the easiest
way would be to create three databases. There are tools around here to
automatically synchronise the schemas of multiple databases in case of a
change in design and if you want some reports to amalgamate these three
databases, it's an easy task on SQL-Server to make queries that will extract
the data of all three databases.

If you want to put these datas into the same database, then you will have to
change the schema of your database in order to accomodate for this. Later,
probably that you could use something like SUSER_SNAME to separate all three
sets of users by using either stored procedures or views.
 
Well, if you want to have three sets of data totally separate, the easiest
way would be to create three databases.  There are tools around here to
automatically synchronise the schemas of multiple databases in case of a
change in design and if you want some reports to amalgamate these three
databases, it's an easy task on SQL-Server to make queries that will extract
the data of all three databases.

If you want to put these datas into the same database, then you will have to
change the schema of your database in order to accomodate for this.  Later,
probably that you could use something like SUSER_SNAME to separate all three
sets of users by using either stored procedures or views.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)








- Show quoted text -
Click to expand...

Thank you for responding. Unfortunately, the company has specifically
requested that I use one database only. Is there a way to do this?

many thanks

R
 
Yes, there is a way to do this but probably that you will have to change the
design of the database(s) in order to take into account the separation
between multiple entities inside the same database.

Now, how you would exactly do this? I don't know; I suppose that's one of
the reasons they are paying you to do this.

The first to look at would be to know if these databases are already located
on SQL-Server with an ADP frontend or if they are on another type of system
with another kind of frontend and that you must export these data to
SQL-Server and create the ADP frontend.

If this step is already done, the second step would probably be to start
designing the tables that will hold the information about these three
entities and the various accounts, associate these tables with the tables
already existing by adding the required foreign keys and then modify the
various queries in order to take this new information into account; possibly
by using something like SUSER_SNAME.

A second possibility would be to use the possibility of having two different
tables and views with the same name by using different schemas (or owners)
and use these schemas to differentiate the users. However, using different
schemas other than dbo doesn't work well with ADP and if you already knew
about the use of schemas on SQL-Server, you wouldn't be here asking for the
question.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Well, if you want to have three sets of data totally separate, the easiest
way would be to create three databases. There are tools around here to
automatically synchronise the schemas of multiple databases in case of a
change in design and if you want some reports to amalgamate these three
databases, it's an easy task on SQL-Server to make queries that will
extract
the data of all three databases.

If you want to put these datas into the same database, then you will have
to
change the schema of your database in order to accomodate for this. Later,
probably that you could use something like SUSER_SNAME to separate all
three
sets of users by using either stored procedures or views.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)








- Show quoted text -
Click to expand...

Thank you for responding. Unfortunately, the company has specifically
requested that I use one database only. Is there a way to do this?

many thanks

R
 
Back
Top