Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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 an EXECUTE AS to 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.

share|improve this answer
    
Thanks for the reply. In relation to IT Support accessing from their laptops is there a better way than using the below command to connect to instance on a different domain? Or is this acceptable? runas /netonly /user:domain\user "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" –  Matt F Nov 12 '12 at 14:01
    
That's probably the best way. Alternatively, they could hold the Shift key and right-click the SSMS shortcut, selecting Run As Different User. There are a few options available here: stackoverflow.com/questions/849149/… . None of them are ideal, and it kind of seems like "Which of these subjects the users to the least amount of ongoing pain?" –  Kevin Feasel Nov 12 '12 at 14:28
1  
You can use the ORIGINAL_LOGIN() function to return the identity of the user regardless of impersonation. –  Jon Seigel Nov 12 '12 at 17:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.