Sql-server – Can you DENY access to a certain schema for a user with dbcreator role

permissionssql serversql-server-2016t-sql

I have a database with two schemas core and dyna.

I am using dacpacs to update the database.
I want the user account to only be able to interact with tables (etc) in the core schema.

This is because the tables (10k+) in the dyna schema are generated dynamically and wont be updated by the dacpac, however the dacpack wants to check every single one to see if it will make an update; and this is killing the deployment performance. (6+ hrs in prod vs 10 min in test)

To be able to deploy dacpacs the user must me in the dbcreator (or sysadmin) role.

We have been using a user with the sysadmin role, for deployment, but we are wondering if:
We can create a user in the dbcreator role, but DENY them access to the dyna schema?

So is it possible to: DENY access to a certain schema for a user with dbcreator role?

Best Answer

DENY access to a certain schema for a user with dbcreator role?

Before I answer this, I want to mention what Daniel eluded in his answer. You do not have a user with dbcreator role, it is a login that is member of dbcreator role. As dbcreator is a server-level role.

You can have a user denied control (Alter any schema, delete, execute, insert, select update etc) privilege whose mapped login is a member of dbcreator server-level role.

Lets do a demo of your question.

Login with a account with admin privilege. Run this statement.

USE [master]
GO
CREATE LOGIN [testDBcreator] WITH PASSWORD=N'testDBcreator', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [dbcreator] ADD MEMBER [testDBcreator]
GO


CREATE DATABASE [testDBcreator]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'testDBcreator', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\testDBcreator.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'testDBcreator_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\testDBcreator_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

USE [testDBcreator]
GO

CREATE USER [testDBcreator] FOR LOGIN [testDBcreator] WITH DEFAULT_SCHEMA=[dbo]
GO
CREATE SCHEMA schema1
GO
CREATE SCHEMA schema2
GO
CREATE TABLE schema1.table1 (id int)
GO
INSERT INTO schema1.table1
VALUES (1),(2)
GO
CREATE TABLE schema2.table2 (id int)
GO
INSERT INTO schema2.table2
VALUES (3),(4)
GO

USE [testDBcreator]
GO
ALTER ROLE [db_owner] ADD MEMBER [testDBcreator]
GO
DENY CONTROL ON SCHEMA::schema1 TO testDBcreator
GO

Change connection to login with testDBcreator and run this.

USE [testDBcreator]
GO
SELECT * FROM schema1.table1
GO

You will get following error.

Msg 229, Level 14, State 5, Line 3 The SELECT permission was denied on the object 'table1', database 'testDBcreator', schema 'schema1'.

Now run this.

USE [testDBcreator]
GO
SELECT * FROM schema2.table2
GO

You get the result as expected.

I am not sure if you can run your .DACPAC with this login.

Related Question