Exclusive User ownership on sql server tables

Question

Requirement: User who created tables in a particular schema, should own the tables, other users who got access to that schema should not able to perform any action on that table(including read).

Example:

1. Tables created by ‘User1’ in ‘Schema1’ should be exclusive to the User1 only with (SELECT, CREATE, UPDATE and DELETE)
2. Other Users who got access to ‘Schema1’, should not able to perform any actions on the tables created by ‘User1’

This requirement is expected to be available for users who have access to the 'schema1', so the tables they create is accessible only for them and not for other users.

Answer 1

For the sake of the discussion let's CREATE new LOGIN, new SCHEMA, and new USER.

use master
GO
CREATE LOGIN SO_Login WITH PASSWORD = 'Dont1Use2This3In4Production'
GO

Use AdventureWorks2019
GO
CREATE SCHEMA SO_Schema
GO
CREATE USER SO_User FOR LOGIN SO_Login;  
GO

---

In theory, you could get what you are looking for, by simply have a rule which allows CREATE TABLE on specific schema. Something like: GRANT CREATE TABLE ON SCHEMA::SO_Schema TO public;

In this case we could give everyone the option to CREATE TABLE on the schema and use simple DDL trigger on CREATE TABLE in order to add permissions like SELECT,DELETE,INSERT,UPDATE for the user that created the table.

unfortunately, GRANT CREATE TABLE ON SCHEMA is not supported.

---

To CREATE TABLE you Required to have CREATE TABLE permission in the database and ALTER permission on the SCHEMA in which the table is being created.

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15#permissions-1

This makes the task more complex and probably not recommended in most cases since you will need to provide more permissions than what you really want the USER to have...

---

If you still want to get this work (against the recommendation) then you will need to GRANT ALTER ON SCHEMA and GRANT CREATE TABLE on database to all - all means "public"

use AdventureWorks2019
GO
GRANT ALTER ON SCHEMA::SO_Schema TO public;
GO
GRANT CREATE TABLE TO public;
GO

next, you will need to DENY the unwonted permission since the above will give all USERs a lot more power than you want to!

This can be done by CREATE DDL TRIGGER on the DATABASE for any DDL_DATABASE_LEVEL_EVENTS

https://learn.microsoft.com/en-us/sql/relational-databases/triggers/ddl-event-groups?view=sql-server-ver15

inside the TRIGGER you should check what was the event. If it was something else than CREATE_SCHEMA or the USER that executed the event should not CREATE SCHEMA then you ROLLBACK TRANSACTION;.

Note! Since you do not want to change the trigger each time a new USER need to CREATE TABLE and add the USER name to the hard coded list of users which can CREATE TABLE, it is best to CREATE new ROLE and simply add each USER you need to this ROLE

CREATE ROLE ModifyTable;
GO

In this case that you based on a ROLE like above ModifyTable, you can GRANT ALTER ON SCHEMA and GRANT CREATE TABLE only to the ROLE ModifyTable instead of to public

In addition, in the same TRIGGER if the USER is one of these that should be able to CREATE the table then you should GRAND him permission to INSERT, DELETE, UPDATE, SELECT on the table which he just created.

Remember that if you forget to DENY a permission from this USER or all the rest then you might have a security issue - which is why this is not recommended procedure.

Your best option is to re-0design the system so you will not need this exact recruitment. So... you can do it as I explained here, but it is not recommended for most cases.

A much better approach is NOT to permit USERs to CREATE TABLEs except for these you can trust with all tables. You should CREATE THE TABLEs for your users directly or using application which you control, and give them the permission to use the specific table which they need. ALTER SCHEMA is not recommended permission to give to simple users!

A user with ALTER permission on a schema can create procedures, synonyms, and views that are owned by the schema's owner. Those objects will have access (via ownership chaining) to information in other schemas owned by the schema's owner. When possible, you should avoid granting ALTER permission on a schema if the schema's owner also owns other schemas.

https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-schema-permissions-transact-sql?view=sql-server-ver15