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.







.



Relevant Pages

  • Re: db_ddladmin
    ... the Windows Group through which I grant these rights does not even have a specific default schema nor does it own any schemas. ... Also, if you have a DDL Trigger on the database, make sure it is not interfering with their work. ...
    (microsoft.public.sqlserver.security)
  • Re: Alter table command vs table copy/rename
    ... I guess the bigger question is, why are they changing multiple columns ... What is wrong with the schema ... some debate over here regarding alter table vs. copy/rename. ... table statement rather than the copy/rename method. ...
    (microsoft.public.sqlserver.programming)
  • Re: Permission problems user cant create a table
    ... say the dbo schema it doesnt automatically mean they have permissons ... It does not grant permissions. ... ALTER permission on the schema are needed as well as CREATE TABLE permissions in the database. ...
    (microsoft.public.sqlserver.security)
  • Re: Complete Neophyte Question(s)
    ... granting alter to the schema seems to allow a ... GRANT ALTER ON SCHEMA::DBO TO MyUser ... All of our objects in this database are ...
    (microsoft.public.sqlserver.security)
  • Re: Discovering new relationships
    ... longevity of the database can be appropriately exploited. ... if the schema changes. ... "alter table add column" would be in first position. ...
    (comp.databases.theory)