User tables in master DB

[pvmurphy]

One of our master databases seems to be populated with various user tables, plus the regular system tables. Everything seems to be working OK, but I’m not a 100% confident of it. How could this possible happened, but more important, how do I go about fixing the problem?

Many thanks,

 

[JayKusch]

Happens quite frquently when a bunch of users have access to SQL Server - LOL.

They could be using Query Analyzer and not properly choosing the DB they want, via the drop down box or by using a USE statement, to add table(s) to.

I would first try to identify who ownes the tables, maybe a user name is given. Then you must figure out if it was a user or a 3rd party app or tool that created these tables.

I would send out an E to all users who may use SQL Server that you are going to try and fix the issue. Then I would "RENAME" a table at a time to see what breaks and who hollers. If you are able to identify who ones it and what its purpose is, you could create another DB, move the table(s) and have your table owner rework their code to point to the new DB.

Just an idea!


Thanks

J. Kusch

 

[pvmurphy]

I think this might have happened when we moved databases to a new server; all of the tables are our ‘standard’ user tables. All of these user tables in Master are also in their respective user databases, and they are getting updated properly. So would it be safe just to drop the unwanted tables in master? I printed out all the tables in master and compared to another server, and all of our system tables are there.

Many thanks,

Jim

 

[JayKusch]

I would first rename them as I suggested. Then if nothing breaks and all is well, you could copy them to a temp DB for a little while longer to be safe, and then delete them whenever you feel comfortable.

Have you check to see if there may be data in the tables residing in the Master DB that may not be in the tables of the respective DBs.

Thanks

J. Kusch

 

[pvmurphy]

Jay,

I will follow your advise, thanks for all the help.

Jim