Can I specify a user in triggers?

Hi there,
I have 2 systems. SystemA has database dbA, systemB has database dbB1
to database dbBn. All these databases are MSSQL databases. The dbB1 to
dbBn will update dbA via triggers residing in these dbBs. Looks like
that I have to add the dbB users to dbA in order for the triggers in
dbB to communicate with dbA. However, it tends to have too many users
(there are many dbBs) in dbA if I have to work in this way and it's
not acceptable.
Now I am trying to set a generic user, say common_webuser, for both
dbA and dbBs. the user will login dbBi with a dbBi specific user, then
in the trigger, I want to change to common_webuser before the
statement to update dbA, finally reset the user to the dbBi specific
user. Actually I am trying to trick the trigger that the current user
is common_webuser so that I can do update to dbA. The setuser command
does not work for this purpose. Can anyone give me some suggestions on
how to acheive this? It's very urgent. Your help is highly
appreciated.
Thanks
GaryGary,
do this:
set up a common-named role in all the dbs. Give the role in dbA the access
it needs. Put the users into the role in the respective dbBs, and give the
role the permissions it needs in the respective dbBs. It then should work.
hth
Quentin
"Gary Wang" <garyw@.nomatterware.com> wrote in message
news:b2328f5c.0308061208.6813d12c@.posting.google.com...
> Hi there,
> I have 2 systems. SystemA has database dbA, systemB has database dbB1
> to database dbBn. All these databases are MSSQL databases. The dbB1 to
> dbBn will update dbA via triggers residing in these dbBs. Looks like
> that I have to add the dbB users to dbA in order for the triggers in
> dbB to communicate with dbA. However, it tends to have too many users
> (there are many dbBs) in dbA if I have to work in this way and it's
> not acceptable.
> Now I am trying to set a generic user, say common_webuser, for both
> dbA and dbBs. the user will login dbBi with a dbBi specific user, then
> in the trigger, I want to change to common_webuser before the
> statement to update dbA, finally reset the user to the dbBi specific
> user. Actually I am trying to trick the trigger that the current user
> is common_webuser so that I can do update to dbA. The setuser command
> does not work for this purpose. Can anyone give me some suggestions on
> how to acheive this? It's very urgent. Your help is highly
> appreciated.
> Thanks
> Gary|||Sorry lost the part that your dbA is on a different server than the dbBs.
In this situation, you may want to try this: each user in the dbBs are
assigned to a common remote login in the linked server, and that remote
login has the desired access for the actions needing to be performed.
hth
Quentin
"Quentin Ran" <ab@.who.com> wrote in message
news:#NFwaCGXDHA.1004@.TK2MSFTNGP12.phx.gbl...
> Gary,
> do this:
> set up a common-named role in all the dbs. Give the role in dbA the
access
> it needs. Put the users into the role in the respective dbBs, and give
the
> role the permissions it needs in the respective dbBs. It then should
work.
> hth
> Quentin
>
> "Gary Wang" <garyw@.nomatterware.com> wrote in message
> news:b2328f5c.0308061208.6813d12c@.posting.google.com...
> > Hi there,
> > I have 2 systems. SystemA has database dbA, systemB has database dbB1
> > to database dbBn. All these databases are MSSQL databases. The dbB1 to
> > dbBn will update dbA via triggers residing in these dbBs. Looks like
> > that I have to add the dbB users to dbA in order for the triggers in
> > dbB to communicate with dbA. However, it tends to have too many users
> > (there are many dbBs) in dbA if I have to work in this way and it's
> > not acceptable.
> >
> > Now I am trying to set a generic user, say common_webuser, for both
> > dbA and dbBs. the user will login dbBi with a dbBi specific user, then
> > in the trigger, I want to change to common_webuser before the
> > statement to update dbA, finally reset the user to the dbBi specific
> > user. Actually I am trying to trick the trigger that the current user
> > is common_webuser so that I can do update to dbA. The setuser command
> > does not work for this purpose. Can anyone give me some suggestions on
> > how to acheive this? It's very urgent. Your help is highly
> > appreciated.
> >
> > Thanks
> > Gary
>