What are the challenges you face when working across database platforms? Take the survey
Options

Comparison of system objects

djinni69djinni69 Posts: 13
edited July 10, 2009 7:21AM in SQL Compare Previous Versions
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?

Comments

  • Options
    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
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • Options
    Thank you!!!
  • Options
    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
    ==========================
    Patrick Barry
    Administrateur sénior de bases de données
    Senior Database Administrator
    UAP Inc.
    http://www.uapinc.com
    ==========================
  • Options
    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
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • Options
    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
    ==========================
    Patrick Barry
    Administrateur sénior de bases de données
    Senior Database Administrator
    UAP Inc.
    http://www.uapinc.com
    ==========================
  • Options
    Hi,

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

    Thanks.

    Patrick
    ==========================
    Patrick Barry
    Administrateur sénior de bases de données
    Senior Database Administrator
    UAP Inc.
    http://www.uapinc.com
    ==========================
  • Options
    Michelle TMichelle T Posts: 566 Gold 1
    We've added comparison of permissions on system schemas, and comparison of other system objects is still on the list.
    Software Developer
    Redgate Software
Sign In or Register to comment.