Sql-server – Query to extract all SQL database roles with INSERT and/or UPDATE privileges

This information_schema view (my bold)

Returns one row for each table privilege that is granted to or granted by the current user in the current database.

This doesn't take into account roles etc: it all works on DATABASE_PRINCIPAL_ID which can be seen if you look at the definition of INFORMATION_SCHEMA.TABLE_PRIVILEGES

Personally, I'd just use sys.database_permissions and not bother with the information_schema rubbish. This relies solely on Meta data visibility to filter rows, so you'll see the actual permissions unfiltered by the INFORMATION_SCHEMA.TABLE_PRIVILEGES view which adds a further filter that bollixes you.

For completeness, here is the view. Note the implicit "old style" JOIN :-)

CREATE VIEW INFORMATION_SCHEMA.TABLE_PRIVILEGES
AS
SELECT  
    USER_NAME(p.grantor_principal_id)   AS GRANTOR,
    USER_NAME(p.grantee_principal_id)   AS GRANTEE,
    DB_NAME()                       AS TABLE_CATALOG,
    SCHEMA_NAME(o.schema_id)            AS TABLE_SCHEMA,
    o.name                          AS TABLE_NAME,
    convert(varchar(10), CASE p.type
        WHEN 'RF' THEN 'REFERENCES'
        WHEN 'SL' THEN 'SELECT'
        WHEN 'IN' THEN 'INSERT'
        WHEN 'DL' THEN 'DELETE'
        WHEN 'UP' THEN 'UPDATE'
        END)                        AS PRIVILEGE_TYPE,
    convert(varchar(3), CASE p.state
        WHEN 'G' THEN 'NO'
        WHEN 'W' THEN 'YES'
        END)                        AS IS_GRANTABLE
FROM
    sys.objects o,
    sys.database_permissions p
WHERE
    o.type IN ('U', 'V')
    AND p.class = 1
    AND p.major_id = o.object_id
    AND p.minor_id = 0  -- all columns
    AND p.type IN ('RF','IN','SL','UP','DL')
    AND p.state IN ('W','G')
    AND (p.grantee_principal_id = 0
        OR p.grantee_principal_id = DATABASE_PRINCIPAL_ID()
        OR p.grantor_principal_id = DATABASE_PRINCIPAL_ID())

The first thing you'll want to do is make sure there is an alternate key (usually via a unique constraint) defined in the taskmembers table, that consists of the task id and member id. This key will drive all data manipulation, and the surrogate key won't be involved at all.

Once you have that key in place, I suggest using the MERGE statement to perform data manipulation. This will avoid having to delete a subset of data and re-insert it. Instead, by matching the source and target data based on the alternate key, incremental insert/updates can take place. The MERGE statement also allows you to delete from the target where records don't exist in the source (i.e., a member of the task was removed).

The DELETE/INSERT pattern is really wasteful of resources because of the extra amount of logging involved, and the unnecessary locks that are required while the data manipulation occurs. If this is a "hot" table, this kind of pattern can easily lead to deadlocks (if not using snapshot isolation), and as you're finding out, wasted key space usage. With the MERGE pattern, new keys would only be required when members are actually added to tasks.