We are currently in the process of moving our development, test and live databases on to separate servers and instances and I am looking at how best to manage access for DBA tasks and also IT Support tasks (permission to read/write data but not execute SP's, create tables, etc), mainly in the live environment.
What is the recommended way to provide access for a DBA? Is it good practice to create a windows AD account specifically for the DBA and then add it as a login to SQL Server with sysadmin server role?
What about managing access for IT Support team? We really want to log/audit individual team members so we know who did what, etc. Would this be best done using individual SQL Server Logins with each login mapped to a corresponding user in each database as needed (seems like a lot of maintenance would be required adding/removing users)? Or is there a way to manage access using say a windows AD group with each team member using their own AD account but use the AD group in SQL Server to manage permissions (but still being able to log an individuals actions)?
Also, should IT Support team members login to SQL Server Management Studio (SSMS) from a server in the same domain as the SQL instance is running or can they install SSMS on their laptops and login remotely? Issue here being their laptops are on different domain so windows auth may not work (although I did find a way to launch SSMS from command line and have it login using specified domain credentials), just not sure what best practice is here.