Comparison of system objects

djinni69

Greetings -

I use Red Gate SQL Compare as a way to baseline my SQL Server configurations, ensuring unauthorized changes are always captured. However, I just realized that system stored procedures are not part of this comparison. Is there a way to validate the system stored procedures, especially the security, to ensure EXE rights are not being changed on these objects? I would also like a way to monitor access to system tables.

A real world example would be sp_add_job and sp_start_job. I usually revoke EXE access on these stored procs to Public when locking down my SQL Servers. How can I watch the permissions to ensure noone has ever granted EXE permissions to Public? Is there a way to force SQL Compare to compare system objects?

JonathanWatts

Hi there,

At the moment there is no way for SQL Compare to compare system objects, most users request that we ignore them. However, you make a good case. I will add your request to our list of features for future versions.

Kind Regards,

Jonathan

djinni69

Thank you!!!

pbarry

Hi!

I've been asking for this functionnality (comparing system objects) since 2004. I hope it makes the cut this time. Here are other reasons why I need to compare system objects:

* SOX compliance:
-> Making sure nobody changes system stored procedure
-> Making sure rights are what they should be on system objects
* Making sure patches have been applied on all servers
* Comparing the behavior of installing cumulative Service Packs on a server: for instance, if you install SP3a on SQLServer 2000 (no SP), you won't get the same results as installing SP3a on SQLServer 2000 SP2!
* And finally, we sometime change system stored procedures to correct problems not yet corrected by Microsoft (ex: msdb.dbo.sp_help_jobstep is missing a "ORDER BY" so job steps are sometime displayed in an incorrect order in Enterprise Manager)

Users might have requested that you ignore them, but just adding a checkbox on the options pages asking to "include system objects or not" is not that difficult ;-)

Otherwise, great product

Regards,

Patrick

JonathanWatts

pbarry,

All very good reasons, however as I am sure you will appreciate it is a little more tricky than just adding an option to the dialog. There are things going on behind the UI that will have to adjusted to take this change into account.

We do pay a lot of attention to user opinions here, a lot of the UI features we included in version 6 can be traced back to requests on the forums. However, we cannot fufil every request every time. I will bump this request up the feature list, but right now the 6.1 and 6.2 plans are locked down. Therefore it is not something that is going to be out in the next few months. We may roll this, along with other frequenly requested engine changes into our Katami work, but I cannot make any promises right now.

Regards,

Jonathan

pbarry

Thank you!

P.S. I know it's way more difficult than just adding a checkbox. I was just referring to the fact that not everyone wants to compare "system objects".

Patrick

pbarry

Hi,

I was just wondering if this feature (comparing system objects) was still on your list.

Thanks.

Patrick

Michelle T

We've added comparison of permissions on system schemas, and comparison of other system objects is still on the list.