1

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?

  • 1
    I would mention the version in case there is any difference. So you get an accurate answer. – SqlWorldWide Nov 19 '17 at 0:15
  • 2
    What happened when you tried? – Martin Smith Nov 19 '17 at 12:51
  • I have to make a request with another team to create the user, plus I asked this on the weekend so haven't tried it out yet. Wanted to get some guidance before diving down a rabbit hole. I will up date when I have tried it out. – DarcyThomas Nov 19 '17 at 19:12
  • Answering the question "can I deny access to a certain schema for a user with dbcreator role?" should take about 5 mins if you have a local instance of SQL Server. – Martin Smith Nov 19 '17 at 19:46
2

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.

1

dbcreator is a server-level role that allows its members to create, alter, drop, and restore databases on the instance.

Intuitively - and without having seen your dacpac - I would grant the executing user in question membership in the db_datareader database role, so it can view all objects, as well as CONTROL SCHEMA ON SCHEMA::core, so it can create, alter and drop objects in the core schema as needed.

From there on, I would monitor what types of permission errors you encounter running the dacpac. Judging from your question, I'm guessing that it wants to alter or drop/recreate the database - if that's the case, see if you can fix that when you're building the dacpac.

  • A DAC can only be upgraded by members of the sysadmin or serveradmin fixed server roles, or by logins that are in the dbcreator fixed server role and have ALTER ANY LOGIN permissions. The login must be the owner of the existing database. The built-in SQL Server system administrator account named sa can also upgrade a DAC. docs.microsoft.com/en-us/sql/relational-databases/… – SqlWorldWide Nov 21 '17 at 13:27

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.