I have tried googling this but may well be using the wrong terms... permissions and the like aren't my strong point.

What I want to do is first create a role - eg 'Snr_Analyst' - with certain permissions to read everything, and alter particular schemas in a database. That bit I can do.

I also want that role to be able to grant read permissions on everything in that database to any other user, but not to grant any higher permissions.

Is this possible?

I'm currently using SQL Server 2014.

  • 1
    Never grant ALTER on a schema to a non-admin user. It's trivial to escalate that privilege. Instead assign ownership of the schema to the user ( or a role they belong to). eg alter authorization on schema::foo to Snr_Analyst Feb 12, 2021 at 13:54

1 Answer 1


you can use GRANT ... WITH GRANT OPTION.


this option specifies that the security principal receiving the permission is given the ability to grant the specified permission to other security accounts.


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.