force using a specific SQL Server/Instance in T-SQL

Question

force using a specific SQL Server/Instance in T-SQL

SQLnbe

SSChasing Mays

Points: 601
More actions
August 20, 2014 at 10:24 am

#397228

Hi,
I would like to force this script to look at the Dev Server only to avoid running it accidentally from production if someone doesn't pay attention which server they are connected on and cause some major damage. Below is a simple example I've put together, obviously the real script is updating records ,dropping tables etc. which we don't want to see on happening on Production 😉
USE MyTestDB
Select * From MyTable
So instead of the above I'd like something like this so it's locked down to my Dev01 Server:
USE [Dev01\MSSQLSERVER].MyTestDB
Select * From MyTable
I'm not having any luck with the above syntax, is this even doable?
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jim Arko Old Hand Points: 329 More actions · Answer 1

One thought is to look at @@SERVERNAME. Maybe you could raise an error or something if it doesn't match the name of the dev box?

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

Why don't you remove permissions to developers on production?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

Jim has one idea.

Another way to do it is to script requiring SQLCMD mode, then you can specify the connection in the script. You can include this snippet is borrowed from an SSDT database project publish script:

:setvar __IsSqlCmdEnabled "True"

GO

IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'

BEGIN

PRINT N'SQLCMD mode must be enabled to successfully execute this script.';

SET NOEXEC ON;

END

Which will keep the script from running if SQLCMD mode isn't on and you can also then check @@SERVERNAME and do the same thing the previous snippet does to stop execution.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SQLnbe SSChasing Mays Points: 601 More actions · Answer 4

Jim Arko (8/20/2014)
One thought is to look at @@SERVERNAME. Maybe you could raise an error or something if it doesn't match the name of the dev box?

Jim,

What I have in my script is the actual server name and instance name I got from @@ServerName and @@ServiceName.

The error that I'm getting is :

"Database 'dev01\mssqlserver' does not exist. Make sure that the name is entered correctly"

Obviously the above is not my database name so it's not parsing it properly. Any thoughts?

SQLnbe SSChasing Mays Points: 601 More actions · Answer 5

Jack Corbett (8/20/2014)
Jim has one idea.
Another way to do it is to script requiring SQLCMD mode, then you can specify the connection in the script. You can include this snippet is borrowed from an SSDT database project publish script:
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
BEGIN
PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
SET NOEXEC ON;
END
Which will keep the script from running if SQLCMD mode isn't on and you can also then check @@SERVERNAME and do the same thing the previous snippet does to stop execution.

I'll give this a try thanks. I will update the post once I make some progress.

Jim Arko Old Hand Points: 329 More actions · Answer 6

SQLnbe (8/20/2014)
Jim Arko (8/20/2014)
One thought is to look at @@SERVERNAME. Maybe you could raise an error or something if it doesn't match the name of the dev box?
Jim,
What I have in my script is the actual server name and instance name I got from @@ServerName and @@ServiceName.
The error that I'm getting is :
"Database 'dev01\mssqlserver' does not exist. Make sure that the name is entered correctly"
Obviously the above is not my database name so it's not parsing it properly. Any thoughts?

I apologize for not clarifying, your syntax won't work as the valid syntax for USE (from BOL) is USE { database } so you won't be able to specify the instance there. My idea was to add logic that looks at the @@SERVERNAME value and raises an error or otherwise prevents execution if that returns anything but the name of your dev server.

SQLnbe SSChasing Mays Points: 601 More actions · Answer 7

@jim got it. I'll work with checking the @@ServerName and maybe use the SQLCMD snippet to see if I can come up with a combination that checks before that I'm connected to the proper instance before executing.

thanks for help guys 🙂

Sean Pearce SSCoach Points: 15999 More actions · Answer 8

Using RAISERROR with a severity level of 20 will disconnect the session immediately.

IF (@@SERVERNAME='Prod')

BEGIN

RAISERROR('Incorrect Server', 20, -1) WITH LOG;

END;

GO

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

SQLnbe SSChasing Mays Points: 601 More actions · Answer 9

Sean Pearce (8/22/2014)
Using RAISERROR with a severity level of 20 will disconnect the session immediately.
IF (@@SERVERNAME='Prod')
BEGIN
RAISERROR('Incorrect Server', 20, -1) WITH LOG;
END;
GO

Thanks