Sql-server – Do I need to explicitly grant permissions for a login under the sysadmin role for it to access a custom schema

Quick answer: For developers, you can GRANT CONTROL on that schema.

Background:

• To see what can be GRANTed: GRANT Schema Permissions
• To see what each permission means: Permissions Naming Conventions

The intersection of these two give the schema permission meanings:

• CONTROL implies the rest and is the highest permissions of any securable
• SELECT, DELETE, INSERT, UPDATE is DML on objects in that schema
• EXECUTE to run scalar UDFs and Stored Procedures in that schema
• ALTER, REFERENCES is DDL (CREATE, ALTER, DROP) on objects in that schema
• VIEW DEFINITION lets folk see the code and definitions

Except for CONTROL, these are mostly orthogonal to each other: folk can EXECUTE code or SELECT, from a view but not see the definition (VIEW DEFINITION) of these.

You also have the database and server permissions which are implied higher up then CONTROL on schemas: see Permissions Hierarchy

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.