SQL Server Performance Forum – Threads Archive

Creating Objects in Master Database

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

No, to the best of my knowledge it is not possible anymore. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

[?][?][?]
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".

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

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.

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]

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

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…

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

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

There are lies, and there are d*mn lies… <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

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=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

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>

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

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.

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

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

]]>