I have a database
_Repository and a Login
COMPANY\ANALYST_USERS which contains a handful of employees, and I am using Microsoft SQL Server 2017.
The database has some core tables in it that I want people within the Login to be able to insert rows to, update and delete rows from, but definitely not be able to drop the tables themselves. But I also want users within the Login to be able to create their own tables/views within that database and be able to drop these items they've created. Is there a permissions setup that would allow this?
In Login Properties for
COMPANY\ANALYST_USERS, under User Mapping, I added them as members to the
db_datawriter roles for the
_Repository database but ensured
db_ddladmin was unselected. This however stopped them from being able to create tables/views so I then granted that Login CREATE TABLE and CREATE VIEW permissions for that database. Despite this, they still don't have permissions to create tables or views.