2

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
  • 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 – David Browne - Microsoft Feb 12 at 13:54
4

you can use GRANT ... WITH GRANT OPTION.

https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-transact-sql?view=sql-server-ver15

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

1

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.