How to restrict data by locations -- sql server programming

nospam_mytrash9 NO[at]SPAM bellsouth.net
6/13/2004 4:15:40 AM

If you're using a single DB on SQL Server for all locations, you can restrict
access by location via roles and view(s).

Create a role for each location, then assign either SQL login account or SQL
login NT User/Group to the appropriate role. Create a view on the table
containing the remote site info. Next create views for the other tables you need
to restrict row level access to by doing an INNER JOIN with the view having the
remote site info. Change the RecordSource for the form to the view. Something
like...

CREATE VIEW vSalesPeople AS

SELECT ....
FROM SalesPeople
WHERE (IS_MEMEBER('site1') = 1 AND SalesPeople.Site = '01')
OR (IS_MEMEBER('site2') = 1 AND SalesPeople.Site = '02')
OR (IS_MEMEBER('site3') = 1 AND SalesPeople.Site = '03')
OR (IS_MEMEBER('site4') = 1 AND SalesPeople.Site = '04')
GO

CREATE VIEW vInvoice AS

SELECT ...
FROM Invoice
INNER JOIN vSalesPeople
ON Invoice.SalesID = vSalesPeople.ID

On Sat, 12 Jun 2004 20:17:01 -0700, =?Utf-8?B?RGViYmll?=
[quoted text, click to view]

Debbie
6/13/2004 6:37:01 PM

Mark,
Thanks you very much. That helps alot.
Debbie

[quoted text, click to view]

Debbie
6/13/2004 6:57:01 PM

Mark,
It just occurred to me that if this feature is availabe, why would one use replication?
What criteria would we use to decide whether to use views or replication? Thanks,
Debbie

[quoted text, click to view]

nospam_mytrash9 NO[at]SPAM bellsouth.net
6/14/2004 4:22:42 AM

I'm not familiar with replication under Access 2002, still using Access 2000
Project databases. However, I would say unless the client application doesn't
always have access to the backend database (such as the road warriors),
replication would be appropriate in this situation.

Another possible situation would be if the database is very large and having all
of the users access a central DB is increasing the amount of time it takes to do
DB functions (Select, insert, delete, update, etc.) to an unacceptable level.
Unless you are able to upgrade the server hardware, you may be better off with
replication so that the processing can be distributed.

I'm from the old school where databases were always centralized. I have lived
through the trends of both centralized to distributed and now it look like it's
back to centralized. As long as users have reliable connections back to the DB,
I would say centralized is the way to go (others may disagree). Replication adds
complexity and I would just as soon spend my time developing new DB's and
performance tuning existing DB's instead of having to deal with replication.

On Sun, 13 Jun 2004 18:57:01 -0700, =?Utf-8?B?RGViYmll?=
[quoted text, click to view]

Debbie
6/14/2004 7:52:01 PM

Mark,
Thanks, that was helpful. Take care,
Debbie

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : How to restrict data by locations