There is no need to grant CONTROL on the schema.
The permission required to DROP SCHEMA is either CONTROL on the schema or ALTER ANY SCHEMA at the database level, and that is why your user was able to drop the schema. Removing these two permissions will prevent the role-associated users from creating and droping the schema (unless they have higher level permissions of course).
The required permission to CREATE ALTER and DROP other objects is the CREATE permission for the object type (table\procedure\function\view) combined with ALTER permission on the schema.
You already have these permissions in your script, so all that you have to do is remove the CONTROL permission. For reference, here is a BOL list of DDL statements where You can find the required permission for all object types.
For the lazy, here is your code after removing the unnecessary permission:
CREATE ROLE myrole AUTHORIZATION dbo;
EXEC sp_addrolemember 'myrole', 'myuser';
CREATE SCHEMA myschema AUTHORIZATION dbo;
GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT,
UPDATE, VIEW DEFINITION ON SCHEMA::myschema TO myrole;
GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO myrole;
Thanks for all your comments. I know this thread is pretty old, but I have finally resolved the issue.
The public server role had an endpoint called ConfigMgrEndpoint that had no permission to "Connect". I granted the connect privilege and things started to work for non-sa users.
Under Server Objects>Service Broker I find the ConfigMgrEndpoint.
I have to assume that at one point MS Configuration Manager was being implemented.
I'm not sure how to dis-integrate the service broker end point, and I can't really experiment because this database is in production. Our current plan is to simply deploy an instance of mssql 2012 on a fresh server install and port the database over.
Hopefully someone else gets some benefit from this, as there was never any mention about end points in the articles i read investigating this issue!
Thanks again!
Best Answer
SQL Server knows four types of roles:
The fixed roles are system supplied and you should not change them. In fact trying to change the owner on a fixed server role aborts the connection with a level 20 error.
For the non-fixed roles the following applies:
The owner of a securable has the CONTROL privilege on that securable. That is true for any securable, including databases, schemata, procedures and roles.
In the case of a role there is no additional privilege that is implied. In particular, the role owner is not automatically a member. The role owner also does not have any permissions on the other role members.
What the CONTROL privilege does imply is the ability to add and remove members from the role, change the owner on the role and drop the role.
Keep in mind however that the role owner can add themselves as a member to that role and therefore can easily acquire the permissions granted to the role itself.
This all is true for both server roles and database roles.
I just wrote a blog post about this with a little more detail: http://sqlity.net/en/2169/role-owner/