I have a Database Role named ApplicationUsers
. I want to grant members of this role the ALTER
permission for this same role, so that existing members can add or remove other members. Here is the SQL statement I've tried:
GRANT ALTER ON [dbo].[ApplicationUsers] TO [ApplicationUsers]
I get the following error:
Cannot find the object 'ApplicationUsers', because it does not exist or you do not have permission.
I can confirm that the user account I'm executing the GRANT
command from does have the required permission. If I run fn_my_permissions(NULL, 'DATABASE')
one of the values returned is ALTER ANY ROLE
.
I've double checked that the role exists and that it is spelled correctly. Here is a snipping from SSMS:
What am I missing?
EDIT:
Thanks to the answer from David, I got the command to execute succesfully, however it doesn't actually seem to work. Members of the ApplicationUsers
group still seem to be denied permission to add other members. When looking at the role's properties in SSMS, the permissions are missing:
This is after running the following commands:
GRANT ALTER ON ROLE::ApplicationUsers TO ApplicationUsers WITH GRANT OPTION
It's unusual to allow members of a role to add and remove members or rename the role. But you can do it like this:
GRANT ALTER ON role::[ApplicationUsers] TO [ApplicationUsers]
But it looks like that's not allowed, and the grant silently does nothing. Instead introduce an ApplicationAdmins role and grant the ALTER on ApplictionUsers to that. eg
create role ApplicationUsers
create role ApplicationAdmins
GRANT ALTER ON role::ApplicationUsers TO ApplicationAdmins
grant select to ApplicationUsers
select p.name grantee, perm.*
from sys.database_permissions perm
join sys.database_principals p
on perm.grantee_principal_id = p.principal_id
where class_desc = 'DATABASE_PRINCIPAL'
go
alter role ApplicationUsers add member ApplicationAdmins
alter role ApplicationAdmins add member user1
go
create table ApplicationTable(id int)
go
execute as user='user1'
alter role applicationusers add member user2
select * from ApplicationTable
revert