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.

Code block

GRANT SELECT ON ColorPlate (ColorID) TO testU

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.

Code block

SELECT dp.grantee_principal_id,p.name AS UName
,dp.permission_name,c.name
,OBJECT_NAME(o.OBJECT_ID) AS TabName
FROM sys.database_permissions dp
INNER JOIN Sys.objects O
ON dp.major_id = o.OBJECT_ID
INNER JOIN sys.columns C
ON c.OBJECT_ID = O.OBJECT_ID
AND c.column_id = dp.minor_id
INNER JOIN sys.database_principals P
ON p.principal_id = dp.grantee_principal_id

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.

Conclusion

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.