You shouldn't think about permissions in the sense that you have to own something to make changes to it. All you need to create an object in a schema is the ability to create an object in the database, and authorization on the schema (there are other ways but they are less secure). You also should think about applying the same permissions to n number of ...
The link by @Ross will definitely help you determine syntax and what all of your options are, but for a little more guidance:
There are two database roles that will probably be useful to you, they are db_datawriter and db_datareader. The two of them will probably handle the majority of your CRUD (Create, Read, Update, Delete) operations.
ALTER ROLE ...
We have around 500 schemas, so granting permission to each schema is difficult.
No. It's tedious to do by hand, but it's also a prime candidate for automation, through scripting.
... dynamically created or dropped on a daily basis, so giving permission at each time a schema is created is also a difficult task.
I disagree. You're doing all the "...
The issue here is cross-database ownership chaining, basically once you leave the context of the AdventureWorks database, you don't have the elevated rights that dbo/sa implies and cannot actually execute the procedure in msdb.
You should recreate the stored procedure in msdb instead and grant your unprivileged user CONNECT permission to msdb and EXECUTE ...
The Login must need following permissions to configure availability groups, if the login not already part of sys admin server role:
ALTER ANY AVAILABILITY GROUP
CONTROL AVAILABILITY GROUP
ALTER ANY DATABASE
For detailed information.. and Grant Server Permissions..
ALTER DEFAULT PRIVILEGES only affects tables created by the user who ran the ALTER DEFAULT PRIVILEGES statement.
You'd have to do this:
ALTER DEFAULT PRIVILEGES FOR ROLE read_write IN SCHEMA my_schema
GRANT SELECT ON TABLES TO read_only;
SQL Server documentation should provide the information you need to determine login permissions.