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?

gbn · Accepted Answer · 2011-08-05 19:36:47Z

19

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

edited Aug 5, 2011 at 19:36

answered Aug 5, 2011 at 9:54

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Quassnoi Over a year ago

+1 but this is cheating: you cannot provide a funny message to SET NOEXEC ON!

gbn · Accepted Answer · 2011-08-05 11:47:42Z

7

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.

edited Aug 5, 2011 at 11:47

gbn

434k84 gold badges602 silver badges690 bronze badges

answered Aug 5, 2011 at 9:52

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

1 Comment

Ben Thul Over a year ago

Did you mean "you will have to issue 'SET NOEXEC OFF'"...?

Collectives™ on Stack Overflow

Check database name before running SQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related