September 8, 2012 at 11:21 am
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?
September 8, 2012 at 10:23 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 9, 2012 at 8:03 am
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
September 9, 2012 at 3:41 pm
Exactly, I was just trying to say it in a more positive way. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 9, 2012 at 3:47 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply