Post reply

Admin rights

  • April 13, 2018 at 5:39 am

    #328297

    Good day, i am a  new DBA at work and i discovered that all 6 BI Developers has sysadmin rights which i think it is not advisable.This was done because they rotate on standby.What permissions can i assign them ,not sysadmin?
    Thanks

    T.

  • April 13, 2018 at 5:51 am

    #1986732

    Is this on a production or non-production environment? What tasks do they do on the databases? Once you've identified this then you can restrict their permissions as it's not a good idea for them to have sysadmin.

    Thanks

  • April 13, 2018 at 5:52 am

    #1986733

    tmmutsetse - Friday, April 13, 2018 5:39 AM

    What permissions can i assign them ,not sysadmin?

    You can assign them any permissions you want. You, or they, know better than anyone what permissions they need to do their jobs; a stranger on a website isn't going to know. 🙂

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • April 13, 2018 at 8:35 am

    #1986756

    That entirely depends on what their responsibilities are, if you expect them to perform full sys admin tasks while on call then they need full sys admin.

  • April 13, 2018 at 8:56 am

    #1986762

    Thanks.It's on production and they create reports , do all ddl ,dml  commands  and we don't have a dedicated reporting or intergration server.it has been a year without a dba when i joined so they filled in that role.

    Thanks

  • April 13, 2018 at 12:26 pm

    #1986813

    tmmutsetse - Friday, April 13, 2018 8:56 AM

    Thanks.It's on production and they create reports , do all ddl ,dml  commands  and we don't have a dedicated reporting or intergration server.it has been a year without a dba when i joined so they filled in that role.

    Thanks

    If all they are doing is creating reports - then you could create a separate database for them and grant them ddl_admin, db_datareader, db_datawriter and grant them view definition and grant execute on the defined schemas in that database.  This avoids granting them db_owner - but still allows them to create what they need to support reporting requirements.

    This would leave them with db_datareader on any databases they need to pull data from...and can be even more restrictive if needed.

    If your system isn't already configured with RCSI - that would be something to consider (and test thoroughly - of course).  If you cannot enable that - then consider enabling snapshot isolation so their reports don't have to use NOLOCK to prevent blocking issues.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • April 16, 2018 at 7:20 am

    #1986917

    Thanks William i will do so.

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

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