Deny CREATE on a schema - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Deny CREATE on a schema

Author

Topic

dbradish
Starting Member

24 Posts

Posted - 2011-10-22 : 15:32:08

Question:
How do I allow CREATE rights on "Schema A" but deny CREATE rights on "Schema B"?

Additional Information:
On "Schema A" owned by dbo, the user should have all rights on all schema A bound objects (insert, update, delete, create, alter, drop)

On "Schema B" owned by dbo, the user should only be able to Insert, Update and Delete data rows. They should not be able to alter, drop or create schema B bound objects.

Current Setup:
Server Level Role: public
Database Level Roles:
db_backupoperator (user must be able to backup DB)
db_ddladmin (user must be able to create, alter, drop objects on Schema A)
db_datawriter (user must be able to insert/update/delete rows in all schemas)
db_datareader (user must be able to select from all schemas)
Schema A Permissions: all
Schema B Permissions:
DENY alter, references, & take ownership
GRANT delete, execute, insert, select, & update

If I DENY control on Schema B, my user cannot CREATE objects, but neither can they INSERT, UPDATE or DELETE data.

Please advise.

"Challenge me. I will not give up. I will not succumb to any situation. I will not be afraid to be great." ~ D. M. Abrashoff

Sachin.Nand

2937 Posts

Posted - 2011-10-23 : 08:40:29

Not sure what are you doing wrong.Its working for me.Please try the code below


Create User Usr without Login
Go

Create schema A authorization dbo
Go

Create schema B authorization dbo
Go

Grant  Create Table To usr
Go

Grant Control on Schema:: A to usr
Go

Deny Alter on Schema:: B to usr
Go

Grant Insert,Update,Delete on Schema ::B to usr
Go

--The block below will create and drop a table under schema A
Execute As User = 'Usr'
Go
Create Table A.t (id INT)
Insert Into A.t
Select 1
Drop Table A.t
Revert
Go

--The following block will error out as User 'usr' does not have permission to create a object under schema B
Execute As User = 'Usr'
Go
Create Table B.t (id INT)
Go
Revert
Go


-- This will create a table under schema B using the original login context
Create Table B.t (id INT)
Go

--The following block will insert a value of 1 in table under schema B using user 'usr' context who has the rights for inserts for objects of schema B
Execute As User = 'Usr'
Go
Insert Into B.t
Select 1
Revert
Go

Select * From   B.t

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources