Tuesday, March 16, 2010

Getting column level permision report

If you think about giving column level permission , you'll probably create a view with only needed columns to expose to ,then grant SELECT permission on that view and absolutely DENY access to underlaying tables to the user, right? Also , it depends on the policy of the company but you are able to grant SELECT/UPDATE/DELETE/INSERT operations on column level, have you ever used it:-)? So I recently visited our client who does perfom security permission on column level and wanted to know what are the columns and on what tables that specific user has access? I decided to perform the below testing, see if that might help you.

CREATE TABLE demo (c INT, c2 INT)

/* Grant SELECT permission on column named c on demo table*/
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