Re: Complete Neophyte Question(s)



Correct, ALTER means (apparently) ALTER anything in the schema (tables,
views, etc). I agree that the closure on options, such as being able to
REVOKE ALTER of certain types, etc. is missing. (But this is also more
flexible than what once would have been possible.)

All the best,
RLF
"James" <minorkeys@xxxxxxxxx> wrote in message
news:%239e%23YbzwHHA.4612@xxxxxxxxxxxxxxxxxxxxxxx
Well I don't particularly mind if the user can modify views. But I'd like
to limit it to that. However, granting alter to the schema seems to allow
a user to modify tables and other objects as well. That's certainly not
acceptable. I tried to explicitly deny Create Table for example, but the
user was still able to modify tables, etc. This seems slightly strange to
me. Guess I'm looking at different schemas or like you said, the
controlled environment.

"Russell Fields" <russellfields@xxxxxxxxxx> wrote in message
news:%23Pe$zXywHHA.424@xxxxxxxxxxxxxxxxxxxxxxx
James,

I do not see a way around this through SQL security settings.
Interestingly, if I:

GRANT ALTER ON SCHEMA::DBO TO MyUser
GRANT CREATE TABLE TO MyUser
REVOKE CREATE VIEW FROM MyUser

I can create a dbo.Table, but I cannot create a dbo.View. However, I
can ALTER an existing dbo.View, etc.

There is no way that I can see to work around this, either, since the
following are not valid:
REVOKE ALTER VIEW FROM MyUser
GRANT CREATE ON SCHEMA::DBO TO RFIELDS3

So, if you want users to be able to create their own views and not be
able touch the main system views, their stuff will need to be in its own
schema. If you grant all users ALTER on the same schema, they will be
able to manipulate each other's views.

An alternative is to have them develop their views on a development
database, then submit them to a controlling authority (DBA, etc) to apply
to production. But that is administrative overhead.

RLF



"James" <minorkeys@xxxxxxxxx> wrote in message
news:erJYjixwHHA.4568@xxxxxxxxxxxxxxxxxxxxxxx
One remaining question. Needed to grant a user permission to create
views within the dbo schema. All of our objects in this database are
within the dbo schema. I granted them CREATE VIEW at the database
level. They didn't have permissions for the dbo schema so the Create
View statement failed. The only way I found I was able to get them the
permissions they needed to create a view within the dbo schema was to
grant them ALTER permissions. Consequently this allows them basically
full control over the database. What's the solution here? I'd really
prefer to not have multiple schemas for this particular database.







.