0

I want to create an sql server Custom Database Role which only allows reading some columns of some tables, and nothing else. this is my attempt.

    CREATE ROLE CustomDatabaseRole01
    GO
    GRANT SELECT ON OBJECT::dbo.Table1(Col1,Col2) To 
    CustomDatabaseRole01
    GO
    GRANT SELECT ON OBJECT::dbo.Table2(Col3,Col4) To 
    CustomDatabaseRole01
    GO
    ALTER ROLE CustomDatabaseRole01 ADD MEMBER UserTest01

the script executes, the role is created and UserTest01 is added as a member however if I execute as UserTest01:

SELECT * FROM Table1;

I get all the columns, and if I execute:

SELECT * FROM Table3;

then again I can read Table3, without granting

please could someone explain?

if deny overrides grant could this work?

CREATE ROLE CustomDatabaseRole01
GO
DENY SELECT ON OBJECT::dbo.Table1(c1,c2) TO 
CustomDatabaseRole01
GO
DENY SELECT ON OBJECT::dbo.Table2(c3,c4) TO 
CustomDatabaseRole01
GO    
ALTER ROLE db_datareader ADD MEMBER UserTest01
GO
ALTER ROLE CustomDataBaseRole01 ADD MEMBER UserTest01
GO

no it does not work, I have no ideas left, anybody can help me?

11
  • 1
    UserTest01 has other privileges granted or is a member of another role granting access to the objects. Note that the expected behavior is to fail with an error, as column permissions doesn't trim column metadata. So you might want to create a view that has the columns and simply grant SELECT on that. Aug 29 at 17:01
  • Yes, unless permissions have been granted to the public role, as all users are members of that role. Aug 29 at 21:00
  • I'm trying to create clean users with no permissions, and then create a series of roles that allow them to only read specific columns
    – pablo
    Aug 29 at 23:01
  • is it possible membership to both db_datareader and a second role which first denies some tables and then grants some columns of those denied tables, or just simply denies some columns?
    – pablo
    Aug 29 at 23:13
  • so a second role denying SELECT on specific columns together with db_datareader role would work?
    – pablo
    Aug 29 at 23:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.