SQL Server Performance

removing system administrators from fixed server r

Discussion in 'SQL Server 2005 Analysis Services' started by hernan93, May 25, 2007.

  1. hernan93 Member

    Hello all,

    On Analysis Services 2005 the members of the Administrators local group are also members of the fixed server role, therefore they have full control over Analysis Services databases.

    I think this can be a problem becouse many system administrators don't need full control over AS. Does someone kwon how can I remove those high privileges to the local administrator?

    Thank you.

    Hernan.
  2. satya Moderator

    When you are logged in as an Administrator in Windows and connect to SQL Server, you account maps to BUILTINAdministrator which has sysadmin privilege in SQL Server. You could grant another group specifically for SSAS access by using

    GRANT <ALL or required privileges> TO DOMAINSSASGroup

    You can also add that user a role which has the privileges you want, for instance to the sysadmin role.

    Fyihttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1163744&SiteID=1 &http://www.mssqltips.com/tip.asp?tip1017 for further read on.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. Raulie New Member

    Generally you would want to remove the builtinadminitrators acount when ever possible.

    Raulie



  4. hernan93 Member

    quote:Originally posted by Raulie

    Generally you would want to remove the builtinadminitrators acount when ever possible.

    Raulie





    thank you.
    but how can I do that in Analysis Services?
  5. Raulie New Member

    First verify if doing this will not have a negative impact, there are certain services that will require you to take additional steps.

    http://support.microsoft.com/kb/317746/en-us

    If this does not apply then you can either remove the built in account from management studio or take its sysadmin permissions away.




    thank you.
    but how can I do that in Analysis Services?


    Analysis Services 2005 uses only one Server Role which local admins are automatically members of, in SSAS under properties/security the local admins are hidden therefore cannot be removed.

    If someone knows differently I would like to know how.





  6. hernan93 Member

    In Management Studio right click on the server, select properties, turn on the "Show Advanced (All) Properties" option and scroll down to the bottom and set the "SecurityBuiltInAdminsAreServerAdmins" property to false.

Share This Page