Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2002-07-02 : 13:05:39
|
| I have two databases on a single server, one is the regular working database, and the other is an archive of DB1 i.e. it has views on all tables from DB1, and Orders and Orderdetails tables of its own which consists of archived data from DB1. Archiving is done periodically to keep table sizes under control. I have a stored procedure in DB1 which does the archiving.DB2 can be opened from the front-end application the same way DB1 can to enable users to see archived data. Users probably should be the same in both databases, as the same people will be accessing both. The problem here is that the archived TABLES in DB2 must allow Inserts because data gets inserted from DB1, however, when they open the db from the front-end, I want it to be read only. From the front end I don't know whether they're in the regular DB or the archive DB. What can I do about this?Sarah Berger MCSD |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-02 : 13:59:34
|
| If your archive logic is encapsulated in a proc, then you don't need to grant insert on you archive tables to the users, right?<O> |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-07-02 : 14:11:55
|
quote:
The problem here is that the archived TABLES in DB2 must allow Inserts because data gets inserted from DB1, however, when they open the db from the front-end, I want it to be read only. From the front end I don't know whether they're in the regular DB or the archive DB.
I would infer from this that your preference is to trap exceptions from the data tier when a user attempts to insert into an archive table rather than have the front end prevent the insert altogether. You could certainly do this by giving your users db_denydatawriter access in the archive database, though it is an unusual design.Jonathan Boott, MCDBA |
|
|
|
|
|
|
|
|
Permissions in archive database