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”]
GRANT SELECT ON ColorPlate (ColorID) TO testU
[/codesyntax]
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”]
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
[/codesyntax]
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.