Post reply

Select from system objects in the msdb database

  • May 5, 2004 at 6:56 pm

    #162423

    Hello,

    I'm a newbie. One of our developers needs to "Select from system objects in the msdb database". Would this be secure or should I not give him access?

    Thanks in advance for your help,

    Lawrence

  • May 10, 2004 at 12:22 am

    #505748

    Lawrence,

    I would ask you developer why he specifically requires access to msdb.  None of my developers have access to system tables or system stored procedures because they don't require it.

    If you decide to give this developer access to msdb, make certain you are taking regular backups of it as well as master.

    Good Luck,

    Angela

  • May 10, 2004 at 11:51 am

    #505851

    Angela,

    Currently I have gave the developer access ( SELECT ) to sysjobs and sysjobsschedules tables in msdb database. His comments were "to create/schedule jobs, I need to be able to select from system objects in the msdb database". I would rather not give him access to msdb database but since i'm still a newbie ( still learing ). If you could recommend a better solution for me that would be great. This developer is a trusted person but would still like a more secure solution if possible.

    Thanks in advance,

    Lawrence

  • May 10, 2004 at 1:46 pm

    #505867

    System tables should not be altered (DELETE, UPDATE, or INSERT) by a user nor should a user create user-defined triggers on system tables.

    There is no harm referencing documented columns in SQL Server system tables. Nevertheless, it should be avoided at all costs referencing undocumented columns in system tables.

    Microsoft recommendation for a safe retrieval of information stored in system tables is through the following components:

    o         Information schema views

    o         System stored procedures

    o         Transact-SQL statements and functions

    o         SQL-DMO

    o         Database application programming interfaces (API) catalog functions

    Application developers must be allowed to obtain SQL Server system information through these components, which always are compatible from release to release. Whereas the format of the system tables is dependent upon the internal architecture of SQL Server and in most cases changes from release to release.

    For a list of components to retrieve information from the msdb database see BOL.

    Regards

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply