If you’re using Windows Authentication for your SQL Server database you’ll need to run SQL Server Management Studio (SSMS) as that Windows account to test queries. SSMS normally runs as the user logged in to Windows, but there is a simple way to run it as the database access account.
Just shift-right-click on a shortcut and select “Run as a different user“. (Windows 7)
You’ll be presented with a Windows login prompt. Log in and SSMS will open. Note that the connection dialog has the new user in the Windows Authentication area
Note: You’re now running the program as a different user, so it probably won’t have access to the files you normally use. My fix was to give the account read/write to the folder where I hold my SQL scripts.
This tip will work for other programs too. Let me know how you used it in the comments below.
When looking at long queries you’ll typically need to remember what data is in one of the tables. SSMS allows the creation of shortcut keys that can be used to quickly look at the first row in a table. You’ll then be able to see the column names and some example data.
In SQL Server Management Studio (2012 shown), create a new shortcut key by going to:
Add the beginning of a query to the shortcut key you like, later on you’ll see how the table name will be appended when the query is run.
“SELECT TOP 1 * FROM”
After you click ok on the dialog, and create a new query window, you’ll be able to select a table name in a query and use your shortcut key. The selection in the query analyzer will be appended to your shortcut query and run.
The screenshots are from SQL Server Management Studio 2012, but older versions have the shortcut key option too. I bet it even works in the old Query Analyzer.
Cool eh? Maybe you like to get the top 200 rows to get a better picture of the table’s data? I’m sure you’ll have tons of ideas on cool shortcuts you can run. Be nice and leave me some ideas in the comments.