SQL 2005's Column Level Permissions

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 on column SSN
        
        Execute AS LOGIN  = 'DAFFIDIO\David'  -- IMPERSONATE using "Execute AS"
        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.

See also

THIS POST IS PROVIDED "AS-IS" WITH NO WARRANTIES AND CONFERS NO RIGHTS. Build time: Sun 03/30/2008 . ©2007 Dalun Software. All rights reserved. Back to Article List