This is more of a discussion topic than a direct question. I explain here
in detail our current database security setup and I would like to hear alternatives
from other people out there.

We currently have several servers running SQL Server 7.0 on them with multiple
databases on each server. We use linked servers to access data on other
servers.

Our current security scheme is the following:

- When touching data that belongs to another database (on the same server
or a linked server), we are not allowed to reference tables directly but
must create a view on top of the table and reference that view.

- Our users are defined at the database level. For instance, the Northwind
database would have has its own user (Nwind_User) and the Pubs database would
have its own user (Pubs_User). As a result, Pubs_User has absolutely no
rights in the Northwind database and Nwind_User has no rights in the Pubs
database.

The exception are the views that are created to grant access to users from
other databases. For example, if Pubs_User needs data from the orders table
in the Northwind database, we'd create view v_Orders and grant rights on
it to Pubs_User.

- In addition, we have 2 users per database. A SELECT user that we use in
query analyzer to browse data (SELECT rights only) and a MODIFY user that
has rights to stored procs that can manipulate the data.

DISCUSSION:
So far, this security scheme has worked pretty fine.

The following are what I perceive as problems with this setup:
1. It's cumbersome for a developer to perform impact analysis or adhoc reports
when the data spans multiple databases. In order to accomplish this, we
must deploy new views to all the necessary tables in the other databases
which is quite a pain when you have a decently-normalized schemas.

Solution:
Create a super-user with SELECT rights to all objects other than confidential
ones (salary, etc.).

2. The 2-user scheme (SELECT and MODIFY) creates some ambiguity as to which
user really has access to views in the other databases.

Solution:
The solution to the first problem would almost eliminate the need for a second
user with SELECT rights in each database.

3. Views are built upon views upon views which probably leads to performance
degradation.

Solution:
- Enforce a rule that views can only be nested X levels (preferably 1 or
2 at most)
- Is it necessary to really use the views or can we go against the table
and grant the appropriate rights on the table?

Well, this about covers it. I would love to hear about other security setups
out there and/or flaws with our current one.