January 23, 2006 at 2:45 pm
Hi Guys,
I am encountering a problem and hope you can help me.
We have two DB's on the same server. When a user insert
or update a row in DB1, I need to insert a row in DB2.
The user in DB1 does not have login/user id on DB2, so
he gets a permission error. Is there a work around this?
P.S. I cannot use the guest user account.
Any help would be appreciated.
Thanks.
January 23, 2006 at 2:57 pm
Set up a role in DB2 explicitly to allow the execution of a stored procedure to insert the records into the appropriate table.
Add the user from DB1 to DB2 but make sure that they are only members of PUBLIC (they will be in any case and you can't do anything about that) and your new role.
In DB1 you will have to have a stored procedure that calls the DB2 procedure.
exec DB2.dbo.MyInsertProc @param1, @param2......@param99
As your DB1 user only has access to this one stored procedure then the only security risk is if
January 23, 2006 at 3:03 pm
David,
The problem is I can have multiple users who create new records.
Is there a way I can pass the user to DB2 and it is the same user
always?
Thanks.
January 24, 2006 at 11:21 am
The only way that I can think of is to use an OPENROWSET and predefined connection but that isn't a way I would choose to do it.
Are you using Windows Authentication?
If so make your users part of an Windows Group and sp_grantlogin to the group in DB2 rather than by individual user accounts
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply