SQL Server – Can’t Grant Role Permissions Under Sysadmin Login

Securitysql serversql-server-2012

I'm trying to execute this script from Management Studio:

GRANT ALTER ON ROLE::[AdaptTemplate] TO [AdaptAdmin] AS [AdminSapr]

Execution fails with this message:

Msg 15151, Level 16, State 1, Line 1 Cannot find the role
'AdaptTemplate', because it does not exist or you do not have
permission.

There are existing roles AdaptTemplate, AdaptAdmin and user AdminSapr in database.
User AdminSapr is a member of db_owner. My current login (which I'm trying to execute script from) is a member of sysadmin server role.

What am I doing wrong?

Best Answer

This is happening because the principal in the AS clause needs to have the permission with the "GRANT OPTION". Running this will allow you to run your original code:

GRANT ALTER ON ROLE::[AdaptTemplate] TO [AdminSapr] WITH GRANT OPTION

My reading of Books Online made me think that what you're doing is right since AdminSapr is in the db_owner role but in testing I had to do something like the above to get this to work.

Related Question