Sql-server – How to link to query file that opens in SQL Server Management Studio showing database login window

sql server

Background:

  • I have many tasks at work everyday. I have a query at work that needs
    to be performed everyday(number of queries may increase). I have
    general(good enough) ability in computers, databases and SQL.

Goal:
I want to optimize my work flow, and I want to make it very easy so even if I am not at work somebody with very little knowledge can do it.

Now: SQL is written in Excel file. The SQL text is copied -> Management Studio is opened -> Database login is done -> SQL text is pasted in -> SQL is executed.

Wanted: SQL is stored in file(sql) -> If opened, the database login screen is shown OR login is done automatically -> SQL is executed.

Problem:
The database login screen doesn't seem to be shown when an SQL file is opened(opens if Management Studio is already open).
I either want the login screen to be shown or to be able to include the username and password in the file somewhere so it is done automatically.
(I know this is small, but assume I'm lazy and time is money, and newbies can waste a lot of time 🙂 )

Best Answer

SSMS will not prompt for a login if you are already connected to an instance (e.g. Object Explorer), this is by design. If you want to change connections simply right-click in the query window and go to change connection.

Your request to include the username and password in the file somewhere is bad security. Especially if this particular account has elevated privileges to the environment.

If you are looking to automate the process, allow minimal involvement, allow anyone to execute the queries, and run them against any number of servers then I would suggest building the queries in PowerShell. A simple function that accepts say a server name and the script file can be written around using Invoke-Sqlcmd or .NET code if you are so inclined. Or write a function for each query and then just have them pass in the server name.

somebody with very little knowledge

This is dictated by your documentation of your process. If your process is written to a 3rd grade level then any adult is assumed to be able to perform that process strictly by following your documentation.

Related Question