Tuesday, June 8, 2010

Column level permission

Uh,it's so hard to back to work from the vacation:-).Yesterday,for the first time, I have been faced the requirement to implement a column level permission on the table, have you done something before? A little script below demonstrates it.

USE dbname;
CREATE TABLE demo (c INT, c2 INT)

GRANT SELECT (c) ON demo TO test


EXECUTE AS USER = 'test';

SELECT * FROM fn_my_permissions('dbo.demo', 'OBJECT')
ORDER BY subentity_name, permission_name ;

REVERT

PS: subentity_name tells you on what column given permission.