We can create column level security in SQL Server without
using the view. We can manage the column level security with Database
roles. Below are the demo scripts:
/*Step-1 : create
login */
Create login vitest with password ='test#135'
GO
/*Step-2: Create database user */
Use<Database name >
GO
CREATE user vitest
FOR LOGIN vitest
/* Step-3:
Create a role */
CREATE ROLE rolevtest
/* Step-4: Grant
the select permission on that specific column/s
*/
GRANT SELECT
ON OBJECT::testbox.Box(form_id)TO rolevtest
/*Step-5: Grant
above role to the database user*/
EXEC sp_addrolemember 'rolevtest','vitest';
/* Step-6 :Login
with newly created user and try below */
SELECT Box_id
FROM testbox.Box /* it will work */SELECT Box_id, Box_Name
FROM testbox.Box /* it will not work */
