none
Column level security

    Question

  •  

    Hey,

      I have 3 columns in a table  Ex:

          Select Column1,Column2,Column from TableName

     

    No. 1. Person A should have permission to read values only (Column1, Column3 of the table) -

          2. Person B, should have permission to read only (Column 2).

     

    Here my question is , I have to write one single stored procedure to statisfy both conditions. Which means, if person A execute this stored procedure , he shoud get only column 1 & 3 values . similarly other person b should get column 2 value. Ex:

            Column 1 - Empid

            Column 2 - SSN  (Only for Top user display)

            Column 3 - Join Date

            Person A  & B as a SQL or Windows login

     

    Thanks

     

     

     

     

    Tuesday, April 29, 2008 6:28 PM

Answers

  • disclaimer: - This is very hacky

     

    create table T1 (c1 int, c2 int,c3 int)

    go

    create user U1 without login

    create user U2 without login

    go

    create proc P1

    as

    IF (user_id() = user_id('U1') )

    BEGIN

    select c1,c3 from T1

    END

    IF (user_id() = user_id('U2') )

    begin

    select c2 from T1

    End

    go

    grant exec on P1 to U1,U2

    go

    -- technically these are not needed due to common owner between P1 and T1 a.k.a. ownership chaining

    grant select on [T1](c1,c3) to U1

    grant select on [T1](c2) to U2

    go

    execute as user = 'U1'

    exec P1

    revert

    go

    execute as user = 'U2'

    exec P1

    revert

    go

    create user U3 without login

    go

    grant execute on P1 to U3

    go

    execute as user = 'U3'

    exec P1

    revert

    go

     

    HTH,

    -Steven Gott

    SDE/T

    SQL Server

    Tuesday, April 29, 2008 7:59 PM