Re: Trigger for update
From: David Gugick (davidg-nospam_at_imceda.com)
Date: 11/28/04
- Next message: David Gugick: "Re: Creating a User-defined function."
- Previous message: Kevin_at_test.com: "Re: Question on approach: user selecting many accounts (follow-up)"
- In reply to: maryam rezvani: "Trigger for update"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 28 Nov 2004 00:23:09 -0500
maryam rezvani wrote:
> Hi
>
> I've following structure in Sql server 2000,
>
> in my db called db1 I have a table called EMP with 3 columns
> (EMPID,EMPNAME,EMPDESC)
> which 2 logins has access to modify this DB and specially EMP, the
> user with login1 called(L1)
> can update only the EMPNAME and the user with login2 called(L2)can
> update only EMPDESC,
> and I wrote a trigger for updating on EMP to update a 2th table in
> DB2 to copy the new changes
> for EMPNAME in destination,(NOTE: only L1 has access to DB2 )
>
> DB1 : EMP (EMPID,EMPNAME,EMPDESC) ->L1,L2
> DB2: EMP (EMPID,EMPNAME) ->L1
> the trigger works successfully when I make new changes in empname
> through the L1,
> but when I want to update the value of empdesc through L2,an error
> will be appearred that
> you have no permission to DB2,and it's true,
> Now,how can I solve my problem without permitting L2 to have an
> access to DB2?
> in another word,how can I check in my trigger whether EMPDESC has
> updated take no action,
> and if EMPNAME has updated do the text of trigger,
>
> Any help would be greatly thankful.
Since you are dealing with a cross database query, you can check the
user name using the suser_sname() function in the trigger and only
perform the insert if it's the correct user.
You can use the IS_MEMBER ( { 'group' | 'role' } ) function to see if
the user is member of a group that is allowed access.
A better way might be to use dynamic SQL to determine if the user really
does have access. You can do this using the PERMISSIONS function. That
function only works on objects in the current database, so you need to
use dynamic SQL to check. If the user doesn't even have rights to use
that database, this probably won't work. See BOL for values for update
and delete access. I only check for insert access to the table (value of
8 in the select).
Declare @p int
Declare @nvcPermCheck nvarchar(1000)
Set @nvcPermCheck = N'Use [DB2] Select @p =
(PERMISSIONS(object_id(''Table2'')) & 8)'
Exec sp_executesql @nvcPermCheck, N'@p int OUTPUT', @p OUTPUT
If @p > 0
Print 'has insert access'
Else
Print 'does not have insert access'
-- David Gugick Imceda Software www.imceda.com
- Next message: David Gugick: "Re: Creating a User-defined function."
- Previous message: Kevin_at_test.com: "Re: Question on approach: user selecting many accounts (follow-up)"
- In reply to: maryam rezvani: "Trigger for update"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
