0

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_datareader and 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.

1

Create a new schema that they own. Eg

CREATE SCHEMA ANALYST
ALTER AUTHORIZATION ON SCHEMA::ANALYST TO [COMPANY\ANALYST USERS]

There’s no way to grant them the ability to create tables in a schema and reliably limit their permissions on other objects in that schema.

0

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.

For this situation, you might want to consider creating a new database role and explicitly grant the permissions on each object you need for this Login. For example:

USE [_Repository]
CREATE ROLE [Analyst_Role]
ALTER ROLE [Analyst_Role] ADD MEMBER [COMPANY\ANALYST_USERS]
GRANT ALTER ON [CoreSchema].[CoreTable] TO [Analyst_Role]
GRANT DELETE ON [CoreSchema].[CoreTable] TO [Analyst_Role]
GRANT INSERT ON [CoreSchema].[CoreTable] TO [Analyst_Role]
GRANT UPDATE ON [CoreSchema].[CoreTable] TO [Analyst_Role]

But I also want users within the Login to be able to create their own tables/views within that database...

For this, I would go with David Browne's answer.

I added them as members to the db_datareader and db_datawriter roles for the _Repository database but ensured db_ddladmin was unselected.

I would use the in-built database roles with caution, as the permissions are implicitly granted. For some further reading, have a look at this SQL Server 2017 Permissions Poster.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.