one SQL DB getting info from another - user setup?
From: Pete Ruby (pete.ruby_at_carris.net)
Date: 06/11/03
- Previous message: Ben Anderson: "Help with SQL Agent Security"
- Next in thread: Dan Guzman: "Re: one SQL DB getting info from another - user setup?"
- Reply: Dan Guzman: "Re: one SQL DB getting info from another - user setup?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Wed, 11 Jun 2003 10:48:42 -0400
I have 3 SQL DB's. The first one, called "Main", has a procedure that pulls
tables from our accounting package into the Main DB. In doing so, it
deletes the old tables, and recreates the new ones. This procedure runs
nightly. One of the tables it updates is called ITEMMAST.
I have 2 other databases, call them DB1 and DB2. They are both used in
separate web based apps. Both have views that reference Main.ITEMMAST. DB1
has user assigned to it called (you guessed it) USER1; DB2, USER2. Both
users are SQL type users, listed in the Security\Logins folder in SQL, as
Standard, with Permit access, with DB1, DB2 as their Default database. They
both have no server roles, and both have acess to their own DB, and Main,
with Public Roles.
In the permissions for Main, USER1 and USER2 are listed, but have NO
permissions set (all option boxes are BLANK) for any of the tables in Main,
including ITEMMAST. DB1 and USER1 run fine w/out having any permissions set
for ITEMMAST. DB2 and USER2 have to have the "SELECT" option for ITEMMAST
selected to be able to run the associated views in DB2.
The problem is when we run the procedure to update that table, the
permissions are also lost. i realize we can change the procedure to just
delete the records and then append....
The real question is: does USER1 and USER2 need specific permision to
ITEMMAST? If so, any ideas why USER1 has no problems running views from DB1
that reference ITEMMAST?
Thanx
Pete
- Previous message: Ben Anderson: "Help with SQL Agent Security"
- Next in thread: Dan Guzman: "Re: one SQL DB getting info from another - user setup?"
- Reply: Dan Guzman: "Re: one SQL DB getting info from another - user setup?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
