Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create objects only in one schema Expand / Collapse
Author
Message
Posted Saturday, September 8, 2012 11:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, January 13, 2013 11:24 PM
Points: 93, Visits: 259
Hi All,

The issue is I have to give permisions to a user just to create objects in only one schema and deny creations in other schemas.

I am a little confused here. If I give a database scoped permission to create table then the user will be able to create tables in all the schemas.

I was planning to give a exculsive deny create on all the schemas other than the schema in which user can create the tables.

But there is no schema scoped permission to deany table creation.

Can any one help me acheive this goal?
Post #1356397
Posted Saturday, September 8, 2012 10:23 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
First, you should *not* need to use any DENYs unless you've already given your user implicit permissions that you need to override explicitly. And in general you do not want to use DENY unless you absolutely have to because it really tends to provoke unintended side-effects.

The Database permission CREATE TABLE does allow a user to Create Tables, but, it alone does not give them any access to any of the schemas within your database. If you also give the user the ALTER permission on a schema, then the two permissions in combination will allow them to CREATE TABLE in that schema. But they can *only* create tables and *only* within that schema.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1356445
Posted Sunday, September 9, 2012 8:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:57 PM
Points: 7,094, Visits: 12,583
na1774 (9/8/2012)
Hi All,

The issue is I have to give permisions to a user just to create objects in only one schema and deny creations in other schemas.

I am a little confused here. If I give a database scoped permission to create table then the user will be able to create tables in all the schemas.

I was planning to give a exculsive deny create on all the schemas other than the schema in which user can create the tables.

But there is no schema scoped permission to deany table creation.

Can any one help me acheive this goal?

In other words, your assertion above (bolded added) is incorrect.

USE [YourDatabase];
GRANT CREATE TABLE TO [YourUser];
GRANT ALTER ON SCHEMA::[YourSchema] TO [YourUser];



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1356478
Posted Sunday, September 9, 2012 3:41 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Exactly, I was just trying to say it in a more positive way.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1356510
Posted Sunday, September 9, 2012 3:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:57 PM
Points: 7,094, Visits: 12,583
Maybe. People learn in different ways. It was stated, but may not have been completely clear to the OP or other readers what you were pointing out so I added a more direct approach. Hopefully no feelings were hurt in the construction of this thread

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1356512
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse