July 27, 2017 at 8:04 am
I've been tasked with making sure all users, except for a couple, have at most select permission on some tables. All of the users are in various roles which gives them a predefined set of permissions. The users that need access to modify these tables are mixed in a role with other users which essentially have freedom to do whatever they want to any object in the database. How can I restrict access to these tables so that a couple of users can modify it them but any other user in the database can only read them? My first thought was to deny alter permission on the objects and then grant the couple of users modify permission but I believe that won't work since the deny will override the alter. Another idea was to create a new role and move only the couple of users that need modify permission to it and deny permission to the rest of the roles. However, I'm trying to avoid this since it leads to another issue when dealing with audits. Any ideas?
July 27, 2017 at 8:08 am
RonMexico - Thursday, July 27, 2017 8:04 AMI've been tasked with making sure all users, except for a couple, have at most select permission on some tables. All of the users are in various roles which gives them a predefined set of permissions. The users that need access to modify these tables are mixed in a role with other users which essentially have freedom to do whatever they want to any object in the database. How can I restrict access to these tables so that a couple of users can modify it them but any other user in the database can only read them? My first thought was to deny alter permission on the objects and then grant the couple of users modify permission but I believe that won't work since the deny will override the alter. Another idea was to create a new role and move only the couple of users that need modify permission to it and deny permission to the rest of the roles. However, I'm trying to avoid this since it leads to another issue when dealing with audits. Any ideas?
You should do this by role, not by user. Assign most users to one role and the elevated abilities users to another role. Then you control permission at the role level. Then when you get new users you don't have to do anything special. You assign them the readonly role and everything else just works.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 27, 2017 at 8:28 am
Yeah, I was scared that would be my only/best option. What about if I deny alter permission to all roles except for the one the users are currently in and then individually deny permission to the rest of the users in that role (around six users)? I realize that requires additional work when a new user is added to the role but I'm giving it one last shot before I create a new role.
July 27, 2017 at 9:31 am
RonMexico - Thursday, July 27, 2017 8:28 AMYeah, I was scared that would be my only/best option. What about if I deny alter permission to all roles except for the one the users are currently in and then individually deny permission to the rest of the users in that role (around six users)? I realize that requires additional work when a new user is added to the role but I'm giving it one last shot before I create a new role.
Don't do this by user at all. You should forget that you can even do that. You will just create a management nightmare for yourself. Security should be role based with very few exceptions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 27, 2017 at 9:44 am
Sean - Thanks for the help! I'll definitely take your advice.
Hopefully one final quick question...I thought that denying alter on a table would prevent users from performing insert, update, and delete while maintaining permission to select but that is not the case. Denying control keeps them from inserting, updating, and deleting but also prevents them from selecting. Is there a permission that covers this or will I have to specify deny insert, deny update, deny delete for each table?
July 27, 2017 at 10:04 am
RonMexico - Thursday, July 27, 2017 9:43 AMSean - Thanks for the help! I'll definitely take your advice.Hopefully one final quick question...I thought that denying alter on a table would prevent users from performing insert, update, and delete while maintaining permission to select but that is not the case. Denying control keeps them from inserting, updating, and deleting but also prevents them from selecting. Is there a permission that covers this or will I have to specify deny insert, deny update, deny delete for each table?
When you deny alter that means you are not allowing that user to alter that table structure, not the data. Yes insert, update and delete are managed separately. You might want to take a peek at the documentation. https://docs.microsoft.com/en-us/sql/t-sql/statements/deny-object-permissions-transact-sql
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 27, 2017 at 12:10 pm
Everything worked. I appreciate the help, Sean!
July 27, 2017 at 12:21 pm
RonMexico - Thursday, July 27, 2017 12:10 PMEverything worked. I appreciate the help, Sean!
You are quite welcome. Glad you were able to get it sorted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply