Trying to establish permissions so a user can only give permissions that they have.

aseigle

I have the requirement where I want to be able to establish permissions for a user, and that user can modify or delete permissions for other users, assuming they have permission to the object.

I've read in the following MSDN article that in 2009, it was possible with a role called SECURITY. https://msdn.microsoft.com/en-us/library/dd568744(v=nav.60).aspx Here is the exerpt:

Can access tables and functions that are related to security information, which include users and permissions. Users with this role can grant permissions to others but can only grant permissions that they have. Therefore, if you want to create an "area superuser," then you should give the person the SECURITY role and permissions for the areas, such as Purchases & Payables, for which they can grant and revoke permissions for other users.

The same MSDN article for 2016 changes the description slightly, and implies the same is not true. https://msdn.microsoft.com/en-us/library/dd568744(v=nav.90).aspx Where it says:

Can create new users and assign them the same permission sets. Must be able to access the User, User Property, Permission Set, and Access Control tables.

For example, you can create a SECURITY permission set that includes the four required tables and any additional permissions that you want to include. You can then assign this permission set to a user who is a department administrator. This user can then administer permissions for other users in their department.

Only a user who has the SUPER permission set can create and modify a SECURITY permission set. Users who have this permission set can assign the same permissions to other users, but they cannot assign themselves the SUPER permission set.


Does anybody have any experience with this, and have you achieved what I'm looking to do without modifications?

Thanks in advance.

Adam