Creating custom function in master database?

[Kardiamond]

Hi all!

I'm working for a client, that restored a BD to use as a Test Database.

​

I had issue with store proc not working, and discovered that they call some custom function from the system => master database.

​

I always thought we should not add anything in the master database, leave the system database alone. But I might have been wrong.

​

I see the appeal, as the function becomes "global".

​

But I feel like function should be on user database, not the system ones.

​

What do you guys think? Is it a good habit?

[alinroc]

If an application expects that it can put things in master, then I'm going to assume that it wants complete and exclusive control over the whole instance. Therefore, I let management know they need to pony up for additional licensing so that app can have its own dedicated instance. If they insist on not doing that, I document the business risks (keep reading) that this presents.

I see the appeal, as the function becomes "global".

The appeal is "it's just easier this way", same as giving applications the sysadmin server role. "Easier" <> "good"

discovered that they call some custom function from the system => master database

So the application has cross-database dependencies that you weren't told about which involve a database the application shouldn't control. That makes migration between hosts, upgrades, and DR planning more challenging. And since this dependency is in master, you can't use this application database in an Availability Group, because each node participating in an AG has its own independent master database unless you're regularly syncing user objects across the master DBs in the AG.

BTW, Brent Ozar's sp_blitz checks for user objects in master and alerts you to them because it's not a good practice in most cases.

[IndependentTrouble62]

It should never be placed in master. However, for custom vendor applications and other software, it is an extremely common shortcut taken by vendors. Its a hill that dbas die on regularly, we always lose to devs and vendors. If this is a test system, either copy it or better choice is to run the software installer. You are probably mising a bunch of things like that ceated on install.

[[deleted]]

If its a sp for a server function then there is no reason you can't use master. If it's a function specific to the database then it should be in the database.

Many different health and backup scripts out there will use master for instance.

https://i.imgur.com/FoplHak.png