Column Level Permissions

Categories: News, Professional, Scripts, SSC
Comments: No Comments
Published on: September 19, 2011

Did you know that you can grant permissions down to the column level in SQL Server?   Well, if you didn’t know that – you do now.

It is actually rather simple to grant permissions at the column level.  This can be demonstrated by the following script.

[codesyntax lang=”tsql”]


If you want to check out more on that syntax, read here.

And then…

Why is it important to know that you can do this?  Well, it is quite possible you have some of these permissions already in place.  It is possible you may have inherited something like this.  Just maybe there is a business requirement requiring that certain users or groups only have access to certain data within certain columns.

That brings up a new problem then.  How do you find out what columns have specific permissions applied to certain users?  Well, that is actually pretty straight forward.  We can query the system views and determine column level permissions.

[codesyntax lang=”tsql”]


The previous query is a really simple version of how to find this information.  As you can see, I am simply returning the UserName, TableName and ColumnName along with the permission in effect on that column.

You should also be able to see that the mapping between these system views is pretty straight forward as well.  Major_id maps to object_id and column_id maps to minor_id.


This query can be of good use to determine permissions in place for columns in tables within your database.  Furthermore, you can even use this query to simply test your curiosity as you check to determine what has been put into effect in the databases you manage.

There are more complex methods to determine these permissions.  With there being more complex methods, I am sure there are also some easier methods.  Let me know what you do to query these permissions.

No Comments - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *

September 2011
« Aug   Oct »


  • @JanosBerke: Anyone has experience (good or bad) w/ jTDS and connection pooling? #sqlhelp
  • @NaineshBabariya: #sqlhelp I want to move my distributor server to a brand new server. Can some one help me with the steps having minimal downtime.
  • @arcticdba: One table, 1M rows/day, ever-increasing BIGINT+other col as key. Needs to be stored for 10 years. Partitioning? Other ideas? #sqlhelp
  • @MrACutler: Can I swap package conns for project conns in SSIS 2014? Ive conv a project to project-deployment but can only do the first package #sqlhelp
  • @SQLHA: @SivassqlG Assuming FCI and one of 'em, why do you care about setting preferred owners (which is how it's done)? #sqlhelp
  • @SQLHA: @SivassqlG FCI? AG? SQL Server clustering is not specific enough. And one instance? More than one instance? #sqlhelp
  • @SivassqlG: #sqlhelp how to set node priority in SQL server clustering..means we have 3 node cluster active-A,passive-B,passive-C
  • @soteark: Datalength() on xml variable shows half the size of the same xml data in an xml file without formatting. Why and how to solve? #sqlhelp
  • @BrentO: @EvoDBA No, databases in an AG can’t be restored. #SQLhelp
  • @BrentO: @zlthomps When you find yourself using multiple tweets for a question, try hitting up #SQLhelp

Welcome , today is Friday, November 27, 2015