Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Aren't there any limitations in effect on your current role (as compared to the 'real' sysadmin)? –  dezso Dec 7 '12 at 10:08
    
@dezso: no, there are no any limitations. Looks like AdminSapr hasn't sufficient rights to grant alter role. –  Dennis Dec 7 '12 at 10:26
    
Are you SURE you in the right database context? Do a select against the principals catalog view just to verify. –  Thomas Stringer Dec 7 '12 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 '12 at 12:50

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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