Post reply

Sysprotects.columns field

  • April 6, 2004 at 2:09 am

    #84862

    Can anyone explain how the sysprotects.columns field works?

    This stores column level permissions on a given object.

    I've read in BOL that it's a bitmap and if bit 0 is set then all columns in the object designated by the ID field are used. I can't figure out how the bits after that pertain to individually selected columns.

    Thanks

    Ben

  • April 6, 2004 at 3:23 am

    #501833

    It is the column position.

    Here is the sample.

    use pubs

    go

    create table test (c1 int, c2 int, c3 varchar(2), c4 int, c5 int)

    grant select (c3, c5) on test to guest

    select id, uid, action, protecttype, columns  from sysprotects where id = object_id('test') 

    --result 0x28 (101000 binary)

    revoke select (c3) on test to guest

    select id, uid, action, protecttype, columns  from sysprotects where id = object_id('test')

    --result 0x20 (100000 binary)

    revoke select (c5) on test to guest

    select id, uid, action, protecttype, columns  from sysprotects where id = object_id('test')

    grant select on test to guest

    select id, uid, action, protecttype, columns  from sysprotects where id = object_id('test')

    --result 0x01 (1 binary)

    drop table test

  • April 6, 2004 at 5:39 am

    #501842

    Of course! I was reading the bitmask left-to-right instead of the other way round.

    Thanks a lot,

    Ben

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply