Before starting I would like to “Thank You” and Appreciate Prasanna for reading the blog and following with comments / queries.
As a follow up query for my earlier blog detailing about column level security in Reporting Services, there was question asking if this could be extended to SQL Server Database Engine, one of the objectives being that even one should have a consistent view of data irrespective of what front is being used (Be it SQL Server management Studio, Query Analyzer etc).
From: Prasanna Prabhu
Sent: Sunday, August 02, 2009 9:17 AM
Subject: Nice Article – Column Level Security in Reporting Services
Very Nice Article –
I would like to see or hear from you as a new blog or amendment to this one, if the same level of security can be moved from REPORTS to Database itself, such that if the report is generatred by those users using direct access to SQL Server (using Query Analyzer, say), then how do we ensure the security is still intact…
The column level security can be built in Multiple ways in Database (SQL Server 2005 onwards).
Most simplistic manner to achieve this would be to create views and give permissions to end users only to access the views rather than base tables. This ways they would get a consistent view of data (columns and rows) irrespective of what front end they are using.
Views are since ages and we would not deal with them here.
Starting SQL Server 2005, a user at database level can be given permission at column level for a table. See below example.. It implements column level security out of box supported in SQL Server 2005 onwards.
Note: For update column permission, select permission would also be needed. And selective insert and Delete permission can not be granted. Only Select and Update would be possible.
For those interested through Management studio:
=> Select the table or view , Right Click on Table
=> Open the "properties" dialog
=> Go to the "permissions" page
=> If they are not already there, add the users and/or roles that need column level permission
=> Choose "select", "Update", or "References" in the permission list (these are the only ones that support column level permissions)
=> Click on "Column Permissions" near the bottom
=> Choose the permissions that you want
=> Click "OK"
USE [master]--Create Test database to understand column level security.USE [TestColumnLevelSecurity]--Create a table with column where column level security is to be enabled.([i] ASC))--Insert data into table.use master--Create Login who would act as grantee.DEFAULT_DATABASE=[master],DEFAULT_LANGUAGE=[us_english],--Create corresponding user in the databaseUSE [TestColumnLevelSecurity]--This statement provides actual permissions.--Testing if it works by swithcing user contextsetuser 'TestUser'/*Msg 230, Level 14, State 1, Line 1The SELECT permission was denied on the column 'j' of the object 'TestSecurity',database 'TestColumnLevelSecurity', schema 'dbo'.*/-- Reverting back to Sysadmin / dbo role that we were connected to before.