OK, so we're going public. Got to put together the plan on managing access to our production servers.
1. First, what do auditors look at when auditing a db environment?
2. More specifically, as a team of infrastructure folks, most of us here have local admin rights on the box which authenticates via BuiltIn\Administrators. Also, our service account is a sysadm. What is the acceptable #of 'sysadmins' on a prod box (or is there any?) Do we remove the BuiltIn\Admin account?
3. With respect to logons, is turning on Logon Failures sufficient to log?
4. public role - what is expected here?
I have more q's, but I'll wait for some reply to the above. TIA!
I also have to address some SOX issues. I plan to look into the Idera "SQL Compliance Manager" software. I haven't evaluated it yet, so really can't offer any opinion.
There is also an excellent post in this thread by Junk Mail Victim:
we are going thru that also... for internal and external audit response.
auditor is gonna get you on using builtin\administrator... remove it (with the exception of cluster server), and limit the number of people have access to your server, limit the number of people who have privilege as sa and remove if possible any developer who own (dbo or database_owner role) from your prod environment.
There are to get arround the number of SA by putting those people who does sa work in a window's group and their main job is admin those server.
limit the number of people who have access to your server room as well. Also you need to document the baseline and write lots of security policy for everything. Here is one of the sample we used as a guideline.