Can someone guide me on how I could start to implement database security access and auditing policies throughout SQL Server 2008 R2, 2008, 2005? The sysadmins and the securityadmin list are unbelievable. Please help.
asked Apr 16 '11 at 07:58 PM in Default
Here's a list of steps I would take:
answered Apr 18 '11 at 10:25 AM
K. Brian Kelley
What do you mean by "The sysadmins and the securityadmin list are unbelievable"?
I would recommend that if your question is so broad that you would be best off getting a test server set up and then purchasing a couple of books Denny Cherry (@MrDenny) has a book for 2008 that I would recommend - http://www.amazon.co.uk/Securing-SQL-Server-Protecting-Attackers/dp/1597496251/ref=sr_1_1?ie=UTF8&qid=1303034839&sr=8-1 and there are similar for the previous versions.
answered Apr 17 '11 at 02:55 AM
I take that to mean there are accounts showing up under sysadmins and securityadmin fixed server roles that you are asking yourself: Why do they have that access?
I've been there and still deal with that one.
The top two choices you have for auditing that are quick and easy to implement are default trace (which should be on by default) and C2 Auditing. The default trace captures mostly changes to configuration options. Then C2 auditing will capture everything done on the instance of SQL Server. Caution, when you enable C2 auditing if your instance is very active you will need to manage the storage space where the trace files are created, because it can get out of hand very quickly.
The first step I would take though is run these two commands:
Take those list of accounts to your upper management, or whoever your supervisor might be, and ask them who in this list ACTUALLY needs this permission. That is a legitmate concern and should be addressed by management.
answered Apr 17 '11 at 07:43 PM