1

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?

4
  • Aren't there any limitations in effect on your current role (as compared to the 'real' sysadmin)? Dec 7, 2012 at 10:08
  • @dezso: no, there are no any limitations. Looks like AdminSapr hasn't sufficient rights to grant alter role.
    – Dennis
    Dec 7, 2012 at 10:26
  • Are you SURE you in the right database context? Do a select against the principals catalog view just to verify. Dec 7, 2012 at 12:36
  • @ThomasStringer: yes, I'm sure. If I modify script a little to GRANT ALTER ON ROLE::[AdaptTemplate] TO [AdaptAdmin] (so, instead of AdminSapr I'm using security context of my current login), then it being executed successfully.
    – Dennis
    Dec 7, 2012 at 12:50

2 Answers 2

2

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.

0

I have the same issues. It was mentionned to use the with grant options but correct me if I'm wrong but if you add with grant options you also permit that users to grant others permissions when it might not be what you want.

3
  • 1
    This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review Nov 15, 2022 at 4:56
  • Okay sorry about that. I just wanted the person to clarify the thing about with grant but I understand. Nov 15, 2022 at 14:15
  • Your understanding of WITH GRANT is correct. Nov 17, 2022 at 22:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.