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.
Best Answer
Regarding providing DBA access, you might be better-off creating an Active Directory group and adding the DBAs into that group. That way, you can provide group-level permissions and give sysadmin to the group rather than individual users, meaning that when a DBA leaves (or a new one gets hired), you just need to adjust Active Directory rights rather than going into each SQL Server and altering logins. The same applies (though without granting sysadmin!) for other groups of internal users. Windows authentication is more secure than SQL authentication, and if you can use it, you probably should.
Regarding auditing, you can still have individual-level auditing when users are in a group. You can use
SUSER_NAME()to get the username in whatever process you're going to use to log activity. Note that somebody could do anEXECUTE ASto switch their user context, but you should be able to log that as well as the IP address from which the query came, so you'd still be able to correlate activity to a person.The idea of using a jumpstation (in your case, you mentioned a server) is possible. It would make administration a bit easier, especially if the laptop domain does not have a full trust relationship with the domain upon which the SQL Server instances are located. It would probably be a bit annoying for the support personnel, honestly, but if you go that route, I'd recommend a virtualized desktop for each IT support team member. That way, they don't have to deal with server contention issues and you can minimize the pain. If you do go down that route, then the IT support team members could have domain accounts and you'd create relevant Windows groups the same way as the DBA group.