Problem with IS_MEMBER function

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with IS_MEMBER function

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-27 : 07:58:36

Don writes "I have a problem with a trigger that I am woking with. Basically I want to evaluate two conditions using the IS_Member function and if the conditions are met then begin the transaction. The problem is I can only evaluate one of the conditions the other is ignored or skipped for some reason. The code below works but only for one IS_MEMBER evaluation

I am using the following code with an IF block as follows problem area indicated by *** and REMARKED out:

CREATE TRIGGER [dbo].[CountyUpdateTRMod]
ON [County]
FOR UPDATE
AS
DECLARE @RowCount AS int
SELECT @RowCount = @@RowCount
IF @RowCount = 0
RETURN
*** /*IF (IS_MEMBER('db_datawriter')<>1) or (IS_MEMBER('db_owner')<>1)*/
***

IF (IS_MEMBER('db_owner')<>1)
BEGIN
IF ((SELECT COUNT(*)FROM DELETED INNER JOIN [Reports] ON [Reports].[Report ID] = [DELETED].[Report ID]
INNER JOIN modPermissions modP ON modP.RowID = [Reports].[Report ID] AND modP.TableID = 1 ) > 0)
BEGIN
/* select permissions per issue */
IF (SELECT MIN(AllPerms.MaxPerms)
FROM (SELECT modP.RowID, MaxPerms = MAX(modP.Type & 0x02)
FROM DELETED INNER JOIN [Reports] ON [Reports].[Report ID] = [DELETED].[Report ID]
INNER JOIN modPermissions modP ON modP.RowID = [Reports].[Report ID] AND modP.TableID = 1
WHERE (IS_MEMBER(modP.RoleName) = 1)
GROUP BY modP.RowID) AS AllPerms) = 0
BEGIN
RAISERROR (150004 ,16,1, 'UPDATE')
ROLLBACK TRANSACTION
RETURN
END
END
END"

nr
SQLTeam MVY

12543 Posts

Posted - 2005-10-27 : 11:53:21

Rather than
DECLARE @RowCount AS int
SELECT @RowCount = @@RowCount
IF @RowCount = 0
RETURN

I would use
if not exists (select * from inserted)
return

Are you sure the test isn't working?
If the user is sysadmin then he doesn't get right via the database role membership and this might not be giving you what you expect.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources