Post reply

Locking Down Views

  • May 26, 2010 at 8:13 am

    #96814

    Hi there,

    I have a database called Datacube, which has may views that us within the IT Department have created.

    I have User A that has an APD to this database which allows him to view the tables and views.

    I want to limit User A's access so that he can only amend and view the views that he has created AND not allow him to create new views at all.

    I created a new Roles and assigned that user to the role. I then on the Permissions only granted him access to certain views - however he still couldn't amend his views?

    Once I added him to db_owner the permissions started to work. This does though mean he can now create views which I don't want him too?

    What is the best way "if possible" to not allow him access to create views on the datbase - BUT have access to views that he has already created so that he can run them and modify them?

    Thanks

  • May 26, 2010 at 8:22 am

    #1172765

    grant alter on [InsertViewnameHere] to [InsertUserAccountHere]

    Should allow the user to alter the views. Apply this to the views the user has created.

    Remove the db_owner priviledge.

    I would grant view definition and select as well.

    Converting oxygen into carbon dioxide, since 1955.

  • May 27, 2010 at 2:50 am

    #1173178

    Running the following -

    GRANT ALTER on 06Jobs_RT to richard2

    GRANT SELECT on 06Jobs_RT to richard2

    GRANT VIEW on 06Jobs_RT to richard2

    But get the following errors -

    Server: Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'on'.

    Server: Msg 170, Level 15, State 1, Line 2

    Line 2: Incorrect syntax near '06'.

  • May 27, 2010 at 8:42 am

    #1173358

    You can only grant select, insert,update and delete permission on a view. So, don't make use of alter and view...

  • May 28, 2010 at 2:42 am

    #1173787

    Having a nightmare with this -

    The user is now only has a database role of public.

    And I have granted the following permissions for this view -

    06Jobs_RT

    GRANT SELECT on [06Jobs_RT] to ri2

    GRANT INSERT on [06Jobs_RT] to ri2

    GRANT UPDATE on [06Jobs_RT] to ri2

    GRANT DELETE on [06Jobs_RT] to ri2

    I can see via Users - ri2- permissions that the correct boxes are ticket - but still via the ADP via Access when I try to make a cahnge and save - I get the error -

    ADO error: User Does not have permissions to perform this operation on view 06Jobs_RT

    Please advise as it makes no sense why the user can still not save the view, if I have now granted him acces.

    Thanks

  • May 28, 2010 at 6:26 am

    #1173863

    The way I have got around this is by changing the object owner of the views in question, this meant that the user could play about with these particular views but at the same time not create any new ones -

    EXEC sp_changeobjectowner '06Jobs_RT' , 'mhs2\user'

    EXEC sp_changeobjectowner 'ArrearsHBAfterDebit_CurrentTenants_RT' , 'mhs2\user'

    EXEC sp_changeobjectowner 'RT_ArrearsAfterExpectedBenefit' , 'mhs2\user'

    EXEC sp_changeobjectowner 'RT_CustomerProfiling' , 'mhs2\user'

    EXEC sp_changeobjectowner 'RT_GIS_Detail' , 'mhs2\user'

    EXEC sp_changeobjectowner 'RT_JOBS' , 'mhs2\user'

    EXEC sp_changeobjectowner 'RT_location' , 'mhs2\user'

    EXEC sp_changeobjectowner 'RT_LocationCreation' , 'mhs2\user'

    EXEC sp_changeobjectowner 'RT_StockUpdates' , 'mhs2\user'

    EXEC sp_changeobjectowner 'RT_Vulnerabilities' , 'mhs2\user'

    EXEC sp_changeobjectowner 'TenancyAuditProperty&Jobs_RT' , 'mhs2\user'

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply