is it possible to define a role that allows members of that role to add other mebers to that role?

Question

is it possible to define a role that allows members of that role to add other mebers to that role?

rolandpater

Old Hand

Points: 318
More actions
February 16, 2019 at 7:43 am

#377023

Hi,
I have an (simplified) application with the roles 'roleAdmin' and 'roleUser'. Members of the roleAdmin should be able to add other members to the role roleAdmin.
This does not seem to be possible. It is possible to grant permissions to roleAdmin on roleUser but not the role roleAdmin itself.
So my question is, is it possible to define a role that allows members of that role to add other mebers to that role? So users can pass on their privileges.
create user userA without login create user userB without login
CREATE ROLE [roleAdmin] CREATE ROLE [roleUser] -- GRANT VIEW DEFINITION, CONTROL ON ROLE::roleAdmin to roleAdmin -- doesn't seem to do anything GRANT VIEW DEFINITION, CONTROL ON ROLE::roleUser to roleAdmin
exec sp_addrolemember 'roleAdmin' , 'userA' -- Make userA admin
execute as user = 'userA' select IS_ROLEMEMBER ('roleUser', 'userA') -- returns 0 ok select IS_ROLEMEMBER ('roleUser', 'userB') -- returns 0 ok
select IS_ROLEMEMBER ('roleAdmin', 'userA') -- returns 1 ok select IS_ROLEMEMBER ('roleAdmin', 'userB') -- returns NULL, should return '0'
exec sp_addrolemember 'roleUser', 'userB' -- works exec sp_addrolemember 'roleAdmin', 'userA' -- Error: Cannot alter the role 'roleAdmin', because it does not exist or you do not have permission. revert

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

rolandpater - Saturday, February 16, 2019 7:43 AM
Hi,
I have an (simplified) application with the roles 'roleAdmin' and 'roleUser'. Members of the roleAdmin should be able to add other members to the role roleAdmin.
This does not seem to be possible. It is possible to grant permissions to roleAdmin on roleUser but not the role roleAdmin itself.
So my question is, is it possible to define a role that allows members of that role to add other mebers to that role? So users can pass on their privileges.
create user userA without login create user userB without login
CREATE ROLE [roleAdmin] CREATE ROLE [roleUser] -- GRANT VIEW DEFINITION, CONTROL ON ROLE::roleAdmin to roleAdmin -- doesn't seem to do anything GRANT VIEW DEFINITION, CONTROL ON ROLE::roleUser to roleAdmin
exec sp_addrolemember 'roleAdmin' , 'userA' -- Make userA admin
execute as user = 'userA' select IS_ROLEMEMBER ('roleUser', 'userA') -- returns 0 ok select IS_ROLEMEMBER ('roleUser', 'userB') -- returns 0 ok
select IS_ROLEMEMBER ('roleAdmin', 'userA') -- returns 1 ok select IS_ROLEMEMBER ('roleAdmin', 'userB') -- returns NULL, should return '0'
exec sp_addrolemember 'roleUser', 'userB' -- works exec sp_addrolemember 'roleAdmin', 'userA' -- Error: Cannot alter the role 'roleAdmin', because it does not exist or you do not have permission. revert

Try creating your role - RoleAdmin. After you create the role, then make RoleAdmin the owner of the role.

Sue

HandyD Mr or Mrs. 500 Points: 507 More actions · Answer 2

Try adding "WITH GRANT OPTION" to the RoleAdmin permissions, e.g.

GRANT VIEW DEFINITION, CONTROL ON ROLE::roleAdmin to roleAdmin WITH GRANT OPTION AS dbo

See this link also: https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-database-principal-permissions-transact-sql?view=sql-server-2017

rolandpater Old Hand Points: 318 More actions · Answer 3

Sue_H - Monday, February 18, 2019 3:02 PM
Try creating your role - RoleAdmin. After you create the role, then make RoleAdmin the owner of the role.
Sue

Thanks, that seems to fix my problems.

HandyD - Wednesday, February 20, 2019 12:18 AM
Try adding "WITH GRANT OPTION" to the RoleAdmin permissions, e.g.
GRANT VIEW DEFINITION, CONTROL ON ROLE::roleAdmin to roleAdmin WITH GRANT OPTION AS dbo
See this link also: https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-database-principal-permissions-transact-sql?view=sql-server-2017

I tried it, but I still got the errors.