August 22, 2011 at 11:06 am
I need to setup something to monitor any modifications to the sysadmin role across all our SQL instances. The ideal situation would be to use SCOM or Policy Based Management in 2008 R2 to execute X and alert me if there have been any changes to the sysadmin role membership since the last check.
The solution needs to work for SQL 2000, 2005, and 2008 :(.
Has anyone come across this need and developed a solid solution?
August 24, 2011 at 10:38 pm
You are limited with it comes to SQL Server 2000 and 2005 without SCOM. You can look at implementing SQL Server Server side trace to capture the security events, I know SQL Server 2005 profiler has the proper events. However I haven't work on 2000 profiler for a while so am unsure as to events. In SQL Server 2008+ you can use Policy Based Management; however a better option is SQL Server - Server Auditing functionality.
Alterantively you can have a fixed list of people in your sysadmin role and have a job that runs every morning to alert you on each of your servers if that list has changed?
Cheers!
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
August 25, 2011 at 7:33 am
We have SCOM running now, but no current monitors for this.
To have one solution across the board, we could house all current sysadmin IDs (one time dump assuming all should have sa rights) in a table and then run a stored procedure via SCOM to compare all current sysadmin IDs with those in the table and write something to the event logs if there are any variances.
I'd think there would be an easier way. Thoughts?
August 25, 2011 at 1:46 pm
PaceIT,
Unless they removed it from R2, you should be able to use Service Broker to audit someone account modifications.
September 1, 2011 at 3:14 am
Why not look at policy based framework in this case?
Also Powershell scripting comes handy in this case.
-Satya SK Jayanty
SQL Server MVP (Follow me @sqlmaster)
Author of SQL Server 2008 R2 Administration CookBook
SQL Server Knowledge Sharing network
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply