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 *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

September 2011
« Aug   Oct »


  • @SQLTaiob: @MJSwart WHILE 1=1 BEGIN INSERT into sysadmin..__locks SELECT ....., getdate() record_date FROM sys.dm_tran_locks #sqlhelp
  • @Kendra_Little: @MJSwart Extended events! I wrote about this a while back, should update it. #sqlhelp
  • @SQLHA: @tkrussy But you're clearly having a network issue. You may want to look at the cluster log (Get-ClusterLog). #sqlhelp
  • @pmpjr: @tkrussy I left you a comment in the MS forum #sqlhelp
  • @SQLSoldier: @MJSwart sys.dm_tran_locks and serializable isolation level (or holdlock hint if a single query). #sqlhelp
  • @MJSwart: #sqlhelp The Lock event category is too chatty, and sp_whoisactive is point-in-time.
  • @MJSwart: #sqlhelp what's your favorite method of tracing important locks taken by a given sql server query?
  • @tkrussy: .@SQLHA none of WSFC logs show anything abnormal besides the event stating it brought SQL Server (MSSQLSERVER) online. #sqlhelp
  • @tkrussy: .@SQLHA completely understood, looking in EventVwr>Appli and service logs>FailoverClustering as well as Application and System Logs #sqlhelp
  • @SQLHA: @tkrussy No idea where you're looking. This is a WSFC or networking issue, not SQL Server. FCI != WSFC. #sqlhelp

Welcome , today is Tuesday, November 25, 2014