user tables in master db
-
Can anyone think of a reason putting user tables in the master db?
thanx
-
In general this is not a good practice.
In reality, I've usually seen it because someone logged in, ran a quick script and THEN realized that their default database was master. They change the database and re-run the script but never delete out the objects from master.
-
exactly...
i just dropped couple hundred tbls, which were developer's 'ooops...'
-
I don't think developers need access to master. Hopefully they aren't members of sysadmin!
Greg
-
To the max avoid giving permissions for master database to users.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
- generaly it's not a good practise to create userobjects in system databases. (master, msdb, modeldb)
- also when you create a login or grant connect to a server, be sure to set the default-db to a non-system-db.
- try to use the "least priviledge" method or you'll endup with overauthorized production applications.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I agree that it is bad practice, but I have seen a few applications do this in the past. I think it is sometimes convenient to store some information in Master when you have a multi-database application that creates and removes databases within the application. You can always count on the Master database to be there.
I have seen a number of accounting systems do this. Not that I think there are many accounting applications that have a good SQL database design.
-
You might find it useful to store information in a Master database if it should persist beyond restores. For example, a list of databases located on linked servers. If you have Production, Test and Development environments, each will have different references to databases on linked servers. Let's say you store the server and database name in a table, so developers can code environment agnostic stored procedures (ie: they can avoid hard-coding server names.) If you store that table in your Master database, you can restore Test and Dev without having to reset those paths.
Of course, I'd rather see you create a ReferenceData db to store that sort of information. But you might use Master because you know no one can do a restore of Master, so it's "safe". I'm sure there are examples of other information that should be stored or updated in a database, but needs to persist between restores.
Rick
townsends.ca -
Might also find it useful to put your "Numbers" or "Tally" table there along with an auxiliary date table... make sure you keep the code to build them safe for when you upgrade the server to the next rev
There's also some really decent functions that you might want available across all databases without having to maintain them in every database...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply