Search code examples
sql-serverpermissionsaltersql-grant

Can't grant ALTER permission on database role?


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:

Image 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:

Permissions shown in SSMS

This is after running the following commands:

GRANT ALTER ON ROLE::ApplicationUsers TO ApplicationUsers WITH GRANT OPTION

Solution

  • 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