I have created a schema in SQL Azure and granted the following permissions to a database role:
CREATE ROLE myrole AUTHORIZATION dbo;
EXEC sp_addrolemember 'myrole', 'myuser';
CREATE SCHEMA myschema AUTHORIZATION dbo;
GRANT ALTER, CONTROL, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, UPDATE, VIEW
DEFINITION ON SCHEMA::myschema TO myrole;
GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO myrole;
Through the above defined permissions
myuser can create/drop his own schema, so to overcome the problem I tried the ALTER ANY SCHEMA permission. But this permission also denies the user to create/drop tables.
What permissions are required in order to allow the user to do anything within his own schema but not be able to create or drop the schema itself?