How to prevent/detect fetching data from another database on same server

Question

I have discovered a problem in our development where I need some help.
We had some reports developed by an outsourced person, and now I am cleaning up his mess.
The biggest problem is that he has often written select statements using 3 parts, for example

[DB_T].[dbo].[SomeTable]

Whether this is good practice or not, I don't care right now.
My problem is he sometimes fetches data from our TEST database in Production queries.

So I was hoping for some way to detect this.

Let's say my test database is called DB_T and my production database is called DB_P and that the application has a connection string to DB_P Now I discovered query like this

select *
from table1 t1
 join [DB_T].[dbo].[Table2] t2 on t1.id = t2.id

When this query is executed, the resultset will unfortunate contain data mixed from both databases.
My question is, is there some way I can detect this ?
Some of these queries are written in a C# Winforms application, some are in a view.
But all will be executed from a c# WinForms application with a connection string pointing to DB_P

It will take me weeks to dig thru all his queries and look for this (some are build at runtime in string variables)
So it would help if I could detect this somehow, or maybe there is a way to disallow reading from another database. In that case the application would throw an error, which is fine by me, better an error then working with wrong data.

Answer 1

To find objects in the database (views, procedures etc) that use 3 (or 4) part naming, you can use the scripts in this article to search the SQL Dependencies in the database.

SELECT OBJECT_SCHEMA_NAME(objects.object_id) 
 AS referencing_schema_name,
 objects.name AS referencing_object_name,
 objects.type_desc referencing_object_type,
 CASE WHEN referenced_database_name = DB_NAME() 
 AND referenced_server_name IS NULL 
 THEN 'Internal' ELSE 'External' END 
 AS referenced_database_location,
COALESCE(sql_expression_dependencies.referenced_server_name, 
 '<localserver>') AS referenced_server_name,
 sql_expression_dependencies.referenced_database_name,
 sql_expression_dependencies.referenced_schema_name,
 sql_expression_dependencies.referenced_entity_name 
 AS referenced_object_name
FROM sys.sql_expression_dependencies
 JOIN sys.objects
 ON objects.object_id = 
 sql_expression_dependencies.referencing_id
WHERE referenced_database_name IS NOT NULL
ORDER BY referencing_schema_name,referencing_object_name

There is also a script on that article for finding dynamic SQL in objects in the database. This doesn't mean they're using 3-part naming, but at least gives you a starting point for your searching.

As for the code in C#, that is going to be more difficult to find. You might be better placed to take a copy of your production DB and restore it to a non-production server that doesn't have the Test DB on it, then run your application against that database (which is now technically non-production). With the Test DB missing, you should get errors wherever you have references to the Test DB in application code.

You can then iteratively resolve each error and progress further with the application until you've got it working without errors. You could also setup up an Extended Events session filtered by user and database (Test DB) to record sqlserver.sql_statement_completed events. Periodically check this events session to see any queries executing against the Test DB by your production user to further identify problem queries and resolve.

Lastly, as a general best practice - take Test DB off your production server and put it somewhere else. This will guarantee that cross database queries to Test DB will fail (because it won't exist on production) and help prevent these kind of cross-contamination issues between environments. It also means you can treat your production environment as a true production, so the next time you have an external contractor come in to write reports/applications, they can develop in the test environment, and any issues with hard-coded DB or server names will become immediately apparent during deployment.

• GuidoG: Thanks for this, I solved it now by creating a new user for the production database, that only has rights on this database and made sure that all applications use this user. It had the same effect, errors where popping out where the test database was being approached. Anyway, I find your script very usefull to