Hello, all of you, DB Gurus!

I would like you to give your opinion about the way I'm implementing Data
Warehouse on my company, and the ways to enhance it.

If this is not the best place to publish it please let me know, as far as
this is an important question for the future of information architecture for
our enterprise, and I would like to receive a real helper hand... THANKS IN
ADVANCE!!! 8-D

Let's see... For that, I'm going to explain as schematic and brief as I can
how it is implemented today:

We have one SQL Server 2000 with several different databases from 3rd party
programs called SIGRID2, GPCNBD, and DOCUWARE. The database owner for all
databases is dbo, EXCEPT for database GPCNBD that has its own owner called
GPCN, as all the objects (tables, views, etc.) inside it. The structure and
permissions for these databases are untouchable, as their associated programs
are the only ones that can manipulate them.

We extract and mix data from all the databases in several ways (into Excel,
crystal reports, etc.), and every time we need this information it needs to
be "IN REAL TIME"; I mean, that when we request the data, it cannot be for
example, from 10 minutes later...

We also do not have any OLAP technology installed nor working from the time
being.

The path to reach the data, usually is:

From .NET software I create, I'm accessing objects (views, stored
procedures, etc) from OUR custom database called LEITER, on the same SQL
SERVER where the other databases reside.

The views and stored procedures on this database look for data FROM THE
OTHER DATABASES. As OWNERSHIP CHAINING for all the databases on the sql
server are enabled, permissions are guaranteed to work.

Note: when I access to database GPCNBD, as it has another owner, I create my
views on Leiter with the same owner in GPCNBD for not to break the ownership
chaining. I'm also not using dynamic sql as far as it would break ownership
chaining too...

We also have a windows network domain called LEITERMI, and people and groups
associated to it.

For the PERMISSIONS CHAIN to work, I create a group, for example GROUP_APP1,
for each new application, APP1, and add the people that will use APP1 to
this group.

The user starts APP1 and it accesses the database object on LEITER. As the
windows user belongs to GROUP_APP1, and this group is added as a login in the
SQL SERVER and it has explicit permissions to the objects in LEITER, it can
access to data on LEITER's object and the other databases (remember ownership
chaining)

This way, the steps I'm following with each new application are always:

- Create the domain's group for the new application and assign network users
to it
- Add the group as a login on the sql server
- Add the group as a user FOR ALL THE DATABASES I'm accessing on, with
PUBLIC access (I'm NOT touching individual permissions for other objects than
our OWN database called LEITER)

- Create views / stored procedures etc, on LEITER db or take advantage of
the ones already created (I'm usually creating a view for each table on the
3rd party database; I'm also create more complex views sometimes, of course)

- Assign permissions on LEITER db for these objects, to the new domain group.

So when the windows user accesses the objects on LEITER using windows
authentication, there are no problems as ownership chaining works.


Drawbacks:

3rd party creators of these applications assured us that they will NEVER
change the structure of their database, but it is risky. The only thing they
told us that can happen is that some information and fields could be added.

Although I'm not changing the actual structure or permissions for these 3rd
party databases, I'm still adding the group for the whole database with
PUBLIC access... still... fair play ?

I'm waiting eager for your answer... 8-D

Thanks again for causing you a terrible headache 8-D,

--
Roger Tranchez
MCTS

Hello and thanks for your answer.

You said:

[quoted text, click to view]

No, GROUP_APP1 does not have access to GPCNBD because it only have PUBLIC
access to this database, and this type of access does not have any rights for
the objects in that database (as usual).

The GROUP_APP1 HAVE access to the views on LEITER, and because of ownership
chaining, the permissions propagate to the tables on GPCNBD where the VIEW
gets its data; the same is valid for the rest of objects: stored procedures
and UDFs.

With regard to Advisory services, I think It will be better for us to find
some Consulting services in our region (spain) to get deeper knowledge.

Thanks,

--
Roger Tranchez
MCTS
..NET 2005 and DB developer

Hello Roger,

Since there there is limitation to change permissions etc on one specific
database, your method might be a good option.

In my opinion, there might be not necessary to create view for each tables
of user databases such as GPCNBD because the GROUP_APP1 actually has the
proper permission on the tables directly. It might be more efficient to
access the tables directly. since it's not possible to add view/SP on the
user databases, it's appopriate to add them in your own database LEITER.

I agree it's a risk that 3rd party may change their schema sometimes.
However, this is out of control and you may want to limit the SP etc that
is directly dependent on them.

Some issues may require a bit more in depth attention and may fall under
the umbrella of Advisory Services. Microsoft now offers short-term and
proactive assistance for specific planning, design, development or
assistance with installing, deploying, and general "how to" advice via
telephone. For more information:

http://support.microsoft.com/default.aspx?scid=fh;en-us;advisoryservice

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hello Roger,

Yes. If you are dependent on cross-database ownership chain, I think your
method shall be only way that you don't have to change the permission on
the user databases such as GPCNBD, though this may bring more complex when
the source database schema changes.

As for Advisory services, if it's good to you, you may want to contact the
local MS PSS to get more information on this option in your local country.

If you have any further questions, please feel free to let's know. Thank
you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Hello again,

You said:

[quoted text, click to view]

Well, the fact is that I'm not dependent on it. It is only the way I
actually do things.

If there is a better way to access multiple databases without changing them