"sa" account and domain level rights

"sa" account and domain level rights

• Claudio Aguilar

  That's a great explanation Jens. Thank you very much. We are going to schedule an off hours test. First step will be to add the domain "sa" account to the local administrator group on our SQL servers. The second step will be removing the account from the domain administrators group. Then we will test and hope for the best. From what I've read, I did not see anything about the "sa" account requiring domain level rights (only local). So, you re-affirmed my thinking. I really appreciate your input.
  
  

• Luiz Fabio

  Sorry for the confusion. Thanks for the response Jen. We have a domain account called "sa" which was used to create databases on our SQL server so it is the current db owner. We use Windows authentication so that credentials are passed via Active Directory from an end user's network logon to our SQL server. Also, we use the Active Directory "sa" account to log in to SQL and it allows our SQL administrators to create Active Directory / SQL accounts to facilitate Windows authentication. My concern is that if we remove domain administration rights from this account we risk breaking applications that have the "sa" account as the database owner.
  
  

• superbrad

  You are mixing some things up:

  "When we had originally set them up the "sa" account belonged to the domain administrators group."

  As sa is a sql server login it cannot belong to any domain group.

  
  "Our SQL servers use mixed mode authentication and some databases are configured for Windows authentication. "

  Authentication takes place at the server level not the database level, so although you switched on Mixed Auth on Server level you can still have autorized users in the database who are authenticated via SQL Server authentication.

  Server --Authentication
  Database --Authorization

  
  HT, Jens Suessmeyer.

  
  

• mgraul

  OK, lets clarify something.

  For the User DOmain account sa, the following schema is valid:

  Domain --Domain Adminstrator --> (inherits) local administrative rights on the SQL Server -- inherits serverole systemadministrator (in sql server because the local admin group and therefore also the domain admins) are systemadministrators --> inherits dbowner role on the database, because systemadministrator are in the role downers by default.

  So breaking the chains:

  -With revoking domain administrative rights from the user sa won’t break the chain if you grant him local admin rights.

  -With revoking domain administrative rights from the user sa won’t, not giving him local administrative rights on the server won’t break the chain if you put him to the systemadmistrator role on the server.

  -With revoking domain administrative rights from the user sa, not giving him local administrative rights on the server, not putting him in the role systeadminstrators break the chain, though he only has downer access to the database now.

  
  It hoped I clarified that a bit. If you didn’t understand my explanation or you have a further question, don’t hesitate to ask :-)

  HTH, Jens Suessmeyer.

  
  

• GreenStone90

  You don’t even need to put him in the local admin group. Local admins are by default in the systemadministrators groups. But you can also put specific users in that role. So you can further restrict the user to have local admin rights.
  
  HTH, Jens Suessmeyer.
  
• "sa" account and domain level rights