Post reply

Domain administrators

  • January 19, 2004 at 5:12 pm

    #84356

    Hi there,

    Sorry if this question has been asked before...

    When I install a new instance of SQL Server, the domain administrators

    automatically get assigned as System Administrators.  I always take them

    out of the System Administrator Server Role, but when I try to DenyDataReader

    or DenyDataWriter to this group, I get a message saying "cannot use the reserved

    user or role name dbo"...How can I deny read and write access to the

    administrator group?

    tks.

  • January 19, 2004 at 5:23 pm

    #490759

    Can you put them in a custom role and explicitly deny them access to the database?

    Or Better yet, remove the group from the server all together. 

    I am not sure but the sa account needs to be local admin on box but might be in the domain group as well.  This might cause the error you are seeing. 

  • January 20, 2004 at 1:12 am

    #490776

    First things we do is add the windows SQLserver-administrators-group as login and add it to the sysadmins serverrole.

    Then we always remove builtin\administrators from the sqlserver-logins !

    Our SQLservers all run using a windows service account.

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • January 21, 2004 at 7:35 am

    #490974

    What you've seen from the others is correct...

    BUILTIN\Administrators is a member of the sysadmin fixed server role by default. Domain Admins are getting sysadmin rights through this login. All members of the sysadmin role map into the databases as dbo unless you explicitly define them to map differently. This isn't a good idea. Also, even if you could, restricting dbo isn't a good idea because that would affect you, as well, since you map in as dbo.

    Basically, if you are able, remove the BUILTIN\Administrators login or drop it out of the sysadmin fixed server role AFTER you've made sure you've added the group for the DBAs in as sysadmins. Also, if you are on a cluster or if you are using Full-Text Indexing, there are some additional steps you'll want to perform BEFORE touching the BUILTIN\Administrators group. Here's an article I wrote discussing how to do this:

    http://qa.sqlservercentral.com/columnists/bkelley/sqlserversecuritysecurityadmins.asp

     

    K. Brian Kelley
    @kbriankelley

  • January 21, 2004 at 8:13 am

    #490990

    While Brian has the best advice fro this, I typically leave the domain admins in because they are a small select group (here) and know better than to work with the db servers. However, they are handy remote hands people to have in an emergency (like when I'm skiing )

     

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 21, 2004 at 8:18 am

    #490994

    Steve makes a good point. We tend to leave ours in as well in case of emergencies like when they're onsite and we're at home at 3 AM. I happen to be in both groups so I'm an exception to the rule, but generally our domain admins keep their grubby little fingers off the SQL Servers unless a DBA demands otherwise (with promises of non-retribution, etc.).

    K. Brian Kelley
    @kbriankelley

  • January 22, 2004 at 7:20 pm

    #491252

    thanks for all your suggestions.  As we have over 30 people in the domain admin group, I feel I need to remove them....there are only a few in the DBA group...

    thanks again.

     

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

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