Deny create table in [dbo] schema

Question

Post reply

Deny create table in [dbo] schema

markoz.job

SSC-Addicted

Points: 478
More actions
November 19, 2015 at 8:48 am

#322413

Hi all,
I've got database called [test].Every user in this database is allowed to create/drop tables .
Every user has its own schema.
I'd like to force users to create tables only in their own schemas and deny create tables in [dbo] schema.
Is there any way to deny create table in [dbo] schema?
I've tried use:
deny alter on schema ::dbo to [some user]
But it dosn't work, any ideas?
We are using SQL 2008 sp3.
Cheers,
Marcin

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

sounds like every user is in the db_owner role, so that's most likely the issue.

remove all permissions assigned to an existing user, and start fresh.

something like this is what you need to do to make them masters of their own schemas, i think:

USE TEST

GO

CREATE USER ClarkKent For Login ClarkKent;

GO

IF NOT EXISTS(SELECT * FROM sys.schemas where name = 'ClarkKent')

EXEC('CREATE SCHEMA ClarkKent;')

ALTER USER ClarkKent with DEFAULT_SCHEMA = ClarkKent

GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT,

UPDATE, VIEW DEFINITION ON SCHEMA::ClarkKent TO ClarkKent;

GRANT CREATE TABLE TO ClarkKent;

GRANT CREATE PROCEDURE TO ClarkKent;

GRANT CREATE FUNCTION TO ClarkKent;

GRANT CREATE VIEW TO ClarkKent;

EXECUTE AS User='ClarkKent'

CREATE TABLE dbo.Example(ExampleID int identity(1,1) not null primary key,

SomeValues varchar(30) )

/*

Msg 2760, Level 16, State 1, Line 22

The specified schema name "dbo" either does not exist or you do not have permission to use it.

*/

CREATE TABLE Example(ExampleID int identity(1,1) not null primary key,

SomeValues varchar(30) )

CREATE TABLE ClarkKent.Example2(ExampleID int identity(1,1) not null primary key,

SomeValues varchar(30) )

REVERT;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

markoz.job SSC-Addicted Points: 478 More actions · Answer 2

Hi Lowell,

Yes, you are right every user has db_ddladmin role so this is the issue.

Thank you very much for your help.

Cheers,

Marcin