SQL SERVER: Allow a user to create/alter/drop tables within own schema only

Question

I'm currently building a solution where multiple small applications run of a single SQL server database each with their own login and default schema. This solution works fine, but I need to ensure security. The applications has a "designer" element to them and I need to grant create/alter/drop table permissions to only the default schema.

I found a few solutions online, but none actually worked for me during testing.

I'm running sql server 12.0 on Azure. I created a user "DemoApp" with it's schema "DemoApp"

Solution 1: Set the user as the owner of the schema works and restricts access to select etc. outside the schema, but users are able to drop/alter tables of other schemas because of the ALTER permission.

ALTER AUTHORIZATION ON SCHEMA::DemoApp to DemoApp;
GRANT CREATE TABLE, ALTER TO DemoApp;

Solution 2: Change the owner of the schema to dbo and only allow specific permissions within the schema.

ALTER AUTHORIZATION ON SCHEMA::DemoApp to dbo;
GRANT CREATE TABLE TO DemoApp;
GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, UPDATE, VIEW DEFINITION ON SCHEMA::DemoApp TO DemoApp

According to online posts this solution should work, but I still get "CREATE TABLE permission denied in database" when trying to create a table under the DemoApp login.

I would like to run multiple applications on a single database securely where each login/user is restricted to its default schema with full control of objects inside it.

Any advice would be appreciated. Thanks in advance.

Answer 1

NEVER grant non-admins the ability to create or modify objects in a schema owned by dbo. It's utterly insecure.

This should work. You don't grant ALTER at all. DemoApp is the owner of the schema, and can drop or alter objects in that schema:

ALTER AUTHORIZATION ON SCHEMA::DemoApp to DemoApp;
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO DemoApp;