SQL Server Performance

Creating Objects in Master Database

Discussion in 'SQL Server 2005 General Developer Questions' started by abbas110, Feb 22, 2007.

  1. abbas110 New Member

    Hi,

    I am a developer. I have created some stored procedures in Master Database (SQL 2000). Now I am upgrading system to SQL Server 2005. The issue I am facing is that I am unable to create those sps in SQL Server 2005 Master Database.

    Allow update option is still present but functionality is unavailable in SQL 2005.

    Is there any way to create my sps in master database?

    Help in this regard would be highly appreciated.

    Thanks
  2. FrankKalis Moderator

    No, to the best of my knowledge it is not possible anymore.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  3. alzdba Member

    [?][?][?]
    Strange..... I still create 'common' dbo.sp_*** procs and dbo.fn_ functions in sql2005-masterdb.
    I'm logged in with sysadmin privileges;
    I've never tried to mark them "msshipped".
  4. FrankKalis Moderator

    That rings a bell!!!
    You still can create them, but cannot mark them as system object.

    Thanks for the correction. However, if you ask me, if it is advisable, I would say no.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  5. satya Moderator

    True, as it is not a best practice to have such user objects in Master database. WHy not use the user databases in this case.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  6. alzdba Member

    Indeed it is not a best practise, but this is just to have it sitting in one db in stead of x dbs per server, and because in case of sp_s sqlserver is automaticaly scanning masterdb for existance of the sp.

    So - in my case - it is just to prevent others (dbo of their own db) from messing around with "common" sp's and to take advantage of this buildin search of SQLserver. [:I]
  7. madhuottapalam New Member

    abbas may be altering /updating some system table using these SPs... that is not possible in SQL 2005

    Madhu
  8. Haywood New Member

    quote:Originally posted by FrankKalis

    ....but cannot mark them as system object.


    I believe that you can still mark them as system objects. I have my own installer and compile several procedures in master and then mark them as system objects. I've not had any errors in the install routine, and my custom code still works...
  9. FrankKalis Moderator

    And you are sure that they are marked as system object?
    Just asking, because I remember a thread in the private MVP newsgroups where it was said that all code was removed from that procedure, so that it won't do anything anymore but it still exists so that no code out there breaks.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  10. FrankKalis Moderator

    Interesting. I've just tried that myself and it still seems to work. Hm....

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  11. Haywood New Member

    There are lies, and there are d*mn lies... <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  12. FrankKalis Moderator

    Aah, found the thread again. It was during the CTP period of SQL Server 2005 and this procedure was under consideration for being removed/modified/disabled. Obviously nothing has happened since then. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  13. Haywood New Member

    I was just thinking the same thing, that it might be removed at some point. I'll have to go check my test SP2 instance and see if it still works.

    <ramble>I imagine (hope) that they'll leave this in place, and also leave the ability to create objects in master. I think that you would see a lot of third-party apps with problems if they remove either of the two functions. I know I've seen my share of apps written that require objects in master in order to properly function, and were properly designed...</ramble>
  14. MohammedU New Member

    quote:Originally posted by abbas110

    Hi,

    I am a developer. I have created some stored procedures in Master Database (SQL 2000). Now I am upgrading system to SQL Server 2005. The issue I am facing is that I am unable to create those sps in SQL Server 2005 Master Database.

    Allow update option is still present but functionality is unavailable in SQL 2005.

    Is there any way to create my sps in master database?

    Help in this regard would be highly appreciated.

    Thanks

    As every body mentioned... it is not good but what is the error you are getting?
    What are you doing in your procedures? Are you moidifying any system objects in your procedueres...
    What kind of access do you have?


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  15. Adriaan New Member

    quote:Originally posted by alzdba

    Indeed it is not a best practise, but this is just to have it sitting in one db in stead of x dbs per server, and because in case of sp_s sqlserver is automaticaly scanning masterdb for existance of the sp.

    Only if the name starts with sp_ !

    Just any other prefix than sp_ and that little problem is solved.
  16. abbas110 New Member

    hi all,

    Thanks for your comments.

    Let me clarify one more thing:

    1 We have developed a wrapper stored procedures named 'sp_myAddRole' & 'sp_myAddRoleMembers' based on system sps 'sp_addrole' & 'sp_addrolemembers' in sql 2000.
    I am using these stored procedures from front end for creating role and adding a login into selected database role.

    I found out that code of sp_addrole & sp_addrolemembers in sql 2005 is totally changed from 2005.

    Is there any way to use my own sps without doing any modification?
    Or is it possible to write a new sp based on sys.sp_addrole?

    Thanks





    S. M. Abbas
  17. MohammedU New Member

    Even if you change the master db compatabilty to 80 you may have to change your procedure using sys.sp_addrole ...

    sp_addrole in 2000 and 2005 accepts same parameters...
    and in 2000 it is db.sp_addrole where as in 2005 it is sys.sp_addrole...

    Instead of fixing the proc to work as 2000 compatibility mode, I prefer fix it to work in 2005...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

Share This Page