CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
MarkusBee
MarkusBee

Reputation: 142

DENY CREATE permissions on dbo schema

We have a SQL Server 2012 database with a mix of "standard" and "admin" users.

Standard users have their own schemas. Admin users can create objects in dbo schema.

I would like to prevent standard users from creating objects under the dbo schema - and force them to create objects under their own schemas. However, the standard users also need access to objects already in the dbo schema - but should not alter or delete them.

Is there an easy way to do this along the lines of: 

 DENY CREATE TABLE on SCHEMA::dbo  
 DENY CREATE VIEW on SCHEMA::dbo  
 DENY CREATE PROCEDURE on SCHEMA::dbo

Thanks for pointing me in the right direction.

Upvotes: 1

Views: 2088

Answers (1)

Ben Thul
Ben Thul

Reputation: 32737

I just did a spot check on CREATE TABLE and CREATE PROCEDURE, but I suspect that all objects behave this way:

Requires CREATE TABLE permission in the database and ALTER permission on the schema in which the table is being created.

(emphasis mine). So, you should be able to just put in a simple DENY ALTER ON SCHEMA::[dbo] to [some principal here] to prevent them from creating or dropping objects.

Altering existing objects is trickier as you don't need any permissions on the owning schema in order to alter an object. But, unless they have some sort of alter permissions on the (for example) procedure, they won't be able to do this by default.

Upvotes: 2

Related Questions