Check database name before running SQL

Question

In SQL Server Management Studio, I repeatedly manage to run sql scripts against the master database rather the specific database that I intend. I open a SQL file and then click run, repeatedly forgetting to set the database first.

I know you can set a default for a server connection - but then I might end up running sql on the default database rather than my intended one if my intended one is not the default.

USE statements in the sql won't work either - the scripts need to be run against multiple different test databases.

So is there anyway to flag a warning that the script is about to be run against the master database? Or possibly force the selection of a database first before a script is run?

Answer 1

One way is to connect with an account that has permissions only in the required databases. Not sysadmin which is what you are doing now.

This would be normal for developers

If you want to run as sysadmin, add this line to each script

IF DB_NAME() = 'master'
    RAISERROR ('Oi! Stop!', 20, 1)  WITH LOG
GO

This will kill the connection

Edit:

I like Quassnoi's solution because it doesn't require sysadmin rights. But, you can't give feedback or revert.

So, I've been playing with this. It checks most boxes

• amusing message
• no sysadmin needed
• re-runnable (kind of)

There is one issue that an SSMS USE (via the drop down) is ignored (as per Quassnoi's notes), but it's getting there. Any more ideas anyone?

SET NOEXEC OFF;
GO
DECLARE @isOK bit;
BEGIN TRY
    IF DB_NAME() IN ('master', 'protectedDB1', '...')
       RAISERROR ('To Catch Block Only', 16, 1);
    SET @isOK = 1;
END TRY
BEGIN CATCH
    SET @isOK = 0;
    RAISERROR ('Oi! Stop!', 16, 1);
END CATCH
IF @isOK = 0
    SET NOEXEC ON;
GO
SELECT 1;
GO

Answer 2

IF DB_NAME() = 'master'
        SET NOEXEC ON
GO
SELECT  1
GO

SET NOEXEC will make the rest of the statements in the script compiled but not executed.

You will have to issue SET NOEXEC OFF before you will be able to execute anything in this connection (even select the database in the drop down box).

Alternatively just close and reopen the script file, it will close and reopen the connection.