CODEWITHSUNDEEP
sqlsql-serversql-server-2008
whoisearth
whoisearth

Reputation: 4170

sql server - setting permissions in trigger

I have a part of a trigger like so - 

    DECLARE @isInsert TINYINT

    SET @isInsert = (CASE @actionType WHEN 'I' THEN 1 ELSE 0 END)

    SELECT
        (CASE @isInsert WHEN 1 THEN i.groupId ELSE d.groupId END) AS groupId
    INTO #tmpRecordPermissionsToCheck
    FROM inserted i
    FULL JOIN deleted d
        ON i.userId = d.userId
            AND
            i.groupId = d.groupId

-- Stop everything if the user is attempting to edit something they're not entitled to...
--   special case(s): refer above for additional tblServer-specific checks required here
    DECLARE @errMsg VARCHAR(255)

    SELECT @errMsg = 'You do not have permission to edit permissions for group ' + IsNULL(ug.shortName, '')
    FROM #tmpRecordPermissionsToCheck tmp
    LEFT JOIN tblUserGroups ug
        ON ug.groupId = tmp.groupId
WHERE dbo.hasAdministrativePermissionsForGroup(tmp.groupId, dbo.getCurrentUser()) = 0


    IF (@errMsg IS NOT NULL)
    BEGIN
        RAISERROR ( @errMsg, 16, 1 )
        ROLLBACK TRANSACTION
        RETURN
    END

I'm calling a separate function that returns a 0 or 1 bit value.

If I do select dbo.isGlobalAdministrator(dbo.getCurrentUser()) I get a 1.

How do I structure the above code so that the IF (@errMsg IS NOT NULL) can be overridden if dbo.isGlobalAdministrator(dbo.getCurrentUser()) = 1 ?

Upvotes: 0

Views: 43

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28940

How do I structure the above code so that the IF (@errMsg IS NOT NULL) can be overridden if dbo.isGlobalAdministrator(dbo.getCurrentUser()) = 1 ?

When you say overridden,i think you want to bypass errormessage

so just add this above error message

if ( dbo.isGlobalAdministrator(dbo.getCurrentUser()) = 1)
return

Upvotes: 1

Related Questions