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?