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

To: gurucb@hotmail.com

Subject: Nice Article – Column Level Security in Reporting Services

Hey Guru

Very Nice Article –

https://sqlbits.wordpress.com/2009/07/31/column-level-security-in-reporting-services

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…

Any ideas?

Prasanna Prabhu

The column level security can be built in Multiple ways in Database (SQL Server 2005 onwards).

Method 1:

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 Big Grin and we would not deal with them here.

Method 2:

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]
GO
--Create Test database to understand column level security.
CREATE DATABASE [TestColumnLevelSecurity] 
Go
USE [TestColumnLevelSecurity] 
GO
--Create a table with column where column level security is to be enabled.
CREATE TABLE [dbo].[TestSecurity](
      [i] [int] NOT NULL,
      [j] [char](10) NULL,
PRIMARY KEY CLUSTERED 
(
      [i] ASC
))
GO
--Insert data into table.
INSERT INTO [TestSecurity] VALUES (1,'A')
go
use master
go
--Create Login who would act as grantee.
CREATE LOGIN [TestUser] WITH PASSWORD='', 
DEFAULT_DATABASE=[master], 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
--Create corresponding user in the database
USE [TestColumnLevelSecurity]
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
--This statement provides actual permissions.
GRANT SELECT ON OBJECT::DBO.[TestSecurity](i) TO TestUser
Grant Update on object::dbo.TestSecurity (i) to TestUser
--Testing if it works by swithcing user context
setuser 'TestUser'
Select * from TestSecurity 
Select i from TestSecurity
Select j from TestSecurity
/*
Msg 230, Level 14, State 1, Line 1
The 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.
setuser
Select * from TestSecurity

 

Advertisements