Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Schema level permissions

    Fun with schemas!

    I have a database with a few schemas. Let's call them "dbo", "admin" and "reports".

    I am creating a role called report_writers

    This role should have no permissions in the dbo and admin schemas.

    In the reports schema, they should be able to:
    - CREATE, ALTER but not DROP views
    - CREATE, ALTER but not DROP procedures
    - They should not be able to create tables, functions, etc
    - SELECT, EXECUTE on all items (easy done)
    - They should have no permissions to update data (easy done: db_denydatarwiter role)

    Can anyone help me out? Bit stuck on the create/alter perms.

    I'll probably think of more subtleties tomorrow.

    Cheers
    George
    Home | Blog

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Offhand, I don't think this can be accomplished with grant statements. DROP is not a permission unto itself in SQL Server. You could accomplish this with Policy Management, I bet.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    After a little more pondering I think I can rephrase the problem slightly which I hope will make this more easily solvable.

    Brief background:
    I have a couple of guys who write MI for me and their current permission set is too high.
    Replicated server setup: ServerA ("live") and ServerB (replicated copy)
    My guys get read-only access to certain schemas on the replicated databases, and slightly more read permission on the "live" system, as they should access some raw objects to build upon that are not to be exposed on the replicated server.
    They need the ability to create objects in only one schema: "reports". They should not be able to cause any harm in any other schema.
    The DROP permission can be... droppped... from this exercise, as the fact that an item is marked as replicated should cater for this. Any items they create themselves prior to being replicated I am fine with allowing them to sweep up (as they will no doubt make mistakes (e.g. not following correct naming conventions).

    Therefore I think I can get away with granting CONTROL permissions on the "reports" schema, and not granting anything on the others?
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I *think* this works for me:
    Code:
    /* DENY I/U/D across the board */
    DENY INSERT, UPDATE, DELETE
      TO report_writer
    GO
    
    /* Create permissions */
    GRANT CREATE VIEW, CREATE PROCEDURE
      TO report_writer
    GO
    
    /* SELECT Only permissions */
    GRANT SELECT
      ON SCHEMA::reports
        TO report_writer
    GO
    
    /* Greater permissions */
    GRANT CONTROL
      ON SCHEMA::reports
        TO report_writer
    GO
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That seems to have cracked it for my needs!

    The role can create, alter and drop objects in the reports schema only.
    Nobody can drop objects added to replication, negating my worry about drop. Something is only added to replication once it has passed QA.
    They can perform ad-hoc queries to suit their needs.
    They cannot modify any data.

    Their experience as users should also be improved as it hides objects in SSMS that they are not privvy to (despite the object definitions being in source control) as well as allowing them to script-out objects directly if required.

    Pretty pleased with how this hangs together now!
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, if you knew the answer, why the heck did you post in the first place? ;-)

    I think the actual minimum for your (new) requirements is ALTER permission on the schema. I am not quite certain how much more CONTROL brings in to the picture, and my testing last night may have been fouled by doing things a little out of order.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I can't seem to grant ALTER perms though?
    Code:
    GRANT ALTER VIEW
      TO report_writer
    Code:
    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near 'ALTER'.
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    grant alter on schema ::reports to report_writer

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Humm.. I wonder if that will give them permission to create more than they should. I don't want them creating tables, functions, etc. Will have a toy later today hopefully.
    George
    Home | Blog

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Alter is a subset of Control. I am not sure what the differences are, though. That would require me to actually go back and read the article about schema permissions. It looks like ALTER permissions on a schema do not include things like SELECT or EXECUTE permissions, which seems a tad odd, but can easily be tested.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Somehow I just knew I hadn't seen the back of this one...

    When one of the guys tries to ALTER an existing, replicated object they are hit with the following er
    Code:
    Msg 21050, Level 14, State 1, Procedure sp_MSreplcheck_publish, Line 16
    Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation. Contact an administrator with sufficient permissions to perform this operation.
    Msg 3609, Level 16, State 2, Procedure fill_rates, Line 4
    The transaction ended in the trigger. The batch has been aborted.
    The error message and Google suggest that the only "resolution" is to grant db_owner privileges - that's not going to happen!

    Any ideas?
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I think the key here may be "replicated object". As in an object they really should not be tampering with in the first place? Since sp_MSreplcheck_publish is being called, I assume that this table is published rather than subscribed, which is even worse as it can cause other people's code to break. I'd say the user needs to live with the restriction in this case. Why is unsupervised data being replicated, anyway?

  13. #13
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    https://msdn.microsoft.com/en-us/library/ms188354.aspx


    Execute As and wrap it in a proc.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Most objects should not be tampered with - these objects are in other schemas. The report writers should be allowed to modify their views/sprocs to suit changing requirements. I know it's not ideal, but there are a number of justifications for this set up.

    There is a dependency hierarchy which means that they are not able to modify anything that will break anything other than their own reports (or each others). Simply put: each report should have its own object, which the report writers are responsible for.

    Not sure how Execute As is going to help me here, Sean? Wrap what in a proc?
    George
    Home | Blog

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    But when one of those objects they are allowed to modify happens to be replicated somewhere (with other applications/reports presumably using the subscribed version), then they should lose any rights to modify the table without telling anybody about it. How did this table end up published in the first place, anyway?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •