NetInverse Developers Blog

SQL server only allows you to restrict access to the table columns indirectly. The approach is create views and stored procedures and grant them to ceitain users. Now SQL 2005 allows you to manuplate column-level permissions directly. So let’s look at a SQL sample below:

        CREATE TABLE student (
            id      INT,
            name    NCHAR(20),
            ssn     VARCHAR(11)
        )

        INSERT INTO student
        (id, name, ssn) VALUES (1, 'david', '999-12-1234')

        GRANT SELECT (id, name) ON student TO [DAFFIDIO\David]
        DENY SELECT (ssn) ON student TO [DAFFIDIO\David] --DENY SELECT

        Execute AS LOGIN  = 'DAFFIDIO\David'-- IMPERSONATE
        SELECT SUSER_NAME(), USER_NAME()

        SELECT id, name FROM student

        SELECT ssn FROM student 

        REVERT  -- undo IMPERSONATE

Now David can see id and name, but not ssn. He will see an error message as below:

   Msg 230, Level 14, State 1, Line 2
   SELECT permission denied on column 'ssn' of object 'student',
   database 'Test', schema 'dbo'.

The syntax is:

        GRANT { ALL [ PRIVILEGES ] }
              | permission [ ( column [ ,...n ] ) ] [ ,...n ]
              [ ON [ class :: ] securable ] TO principal [ ,...n ]
              [ WITH GRANT OPTION ] [ AS principal ]

You can use GRANT, REVOKE AND DENY to set granular permissions on column level now.