July 17, 2014 at 5:37 am
I have a need to grant a group UPDATE access on a column in a table. While they will be allowed to read the entire table, they are only going to be allowed to update one specific column and they will not be allowed to insert new records.
Right now we're only granting them access in our Dev / QA environments so they can play with what they need to update without affecting production. Because we restore frequently down to these environments, I need a way of doing this in T-SQL (so I can add it to the restore jobs) without having to go into the database properties and do the grid with securables each and every time we do a restore. But I am apparently looking at the wrong things in BOL and Google refuses to yield any answers except how to restrict a group from viewing columns, which is the exact opposite of what I need to do.
Any thoughts on how I can grant column permissions via T-SQL?
July 17, 2014 at 6:52 am
I think I've found a possible solution. I'm going to create a database role with these permissions in Production and then just assign the user group to that role in the restore job. I'll test it, of course, in Dev to see if it works and update this post either way.
July 17, 2014 at 8:06 am
Ah-HA!
While scripting the securables from the database role, I found this interesting piece of code that I don't see in BOL. This, apparently, is the syntax for adding a specific permission to a table's column.
GRANT UPDATE ON [dbo].[MyTable] ([MyColumn]) TO [MyRole/MyUser] AS [dbo]
I swapped out the role name for the user group name in question and the code ran without error. Now the trick is to see if the users can actually do the deed, but I doubt they will have any problems.
WHOO! No need for roles now.
BTW, does anyone else have an issue with going into an existing database role, hitting the script button on the securables screen, and getting the error "There is no action to be scripted"? Why would it allow only scripting during the creation of the role, not afterwards?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply