0

I want to GRANT permissions to a new database role so that defined users can add and remove members from that role WITHOUT granting ALTER ANY ROLE permissions.

Here's how my query runs/dies:

CREATE ROLE [NewDBRole]
GO

The command(s) completed successfully.

GRANT ALTER ON [NewDBRole] TO [domain\username] WITH GRANT OPTION AS DBO
GO

This statement returns this error:

Server: Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'NewDBRole', because it does not exist or you do not have permission.

But clearly the Role exists and I do have permissions, because I am able to DROP it:

DROP ROLE [NewDBRole]
GO

This statement works fine and returns:

The command(s) completed successfully.

Is there special syntax needed for this GRANT, and if so, what syntax will be required to REVOKE it in the future?

  • It appears the CASCADE option will be required for the REVOKE to work. – kunkel Mar 17 '16 at 14:55
1

Look at the syntax diagram a little more carefully. Typically, above object level, you must specify the type you are applying permissions to with <entity_type>::<entity_name>.

GRANT ALTER ON ROLE::[NewDBRole] TO [domain\username] WITH GRANT OPTION AS dbo;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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