In the big picture, you need to:
- Download the free Microsoft SQL Server Management Studio and install it, accepting the defaults
- Connect to your SQL Server
- Create a low-privileged login so that you don’t accidentally drop objects
- Learn to switch back & forth between the low-privileged account and your regular one
Let’s get started!
1. Download and install SSMS (but not on the server itself)
The first step is really easy: go here to get the latest version of SSMS and install it. If you already have a version of SSMS, the installer will automatically update it to the latest version. It doesn’t matter what version of SQL Server you’re running in production: as long as you’re running a currently supported version of SQL Server (2012 & newer as of this writing), you always wanna run the latest version of SSMS, which will include a ton of bug fixes.
Do this installation on your desktop or laptop, not on the SQL Server itself. Over time, you’ll learn that running SSMS (as with any other app) on the SQL Server itself will slow it down. You wouldn’t remote desktop into the SQL Server and start playing Fortnite, now, would you? Don’t answer that. You probably would. You’re the kind of person who reads this blog, after all, and I … let’s just stop there. Install SSMS on your desktop.
2. Connect to your SQL Server
After launching SSMS, you get a connection dialog:
Server name – the DNS name or IP address where your SQL Server answers connection requests. This is usually the same as the server name itself, but if you have fancier setups like named instances or non-default port numbers, you’ll need to specify those here.
Authentication – probably Windows, try that first. If that fails, you either don’t have access to the server, or it’s configured with SQL authentication. You might have a username & password on a post-it note somewhere, like from the person who installed it, and it might have a username of “sa”. In that case, go ahead and use that for now. In the next step, we’ll set you up a low-privilege account.
Click Connect, and you’ll be handsomely rewarded with a window that looks like this:
At this point, you’re able to do … all kinds of dangerous things, actually. We need to fix that so you don’t do something stupid.
3. Create yourself a low-privileged login.
Start a new query by clicking File, New, Database Engine Query, or right-click on the SQL Server name and click New Query:
You’ll get an empty new-query window. Copy/paste the below into your new query window so we can find out if you have Windows-only authentication turned on:
|
1 |
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') |
If the result is 1, that means your SQL Server only allows Windows logins. In that case, I deeply apologize, but I’m not covering that yet in the scope of this blog post.
If the result is 0, good news! You can create SQL logins. To do that, copy/paste the below into your new-query window, and note that you have some changes to make:
|
1 2 3 4 5 6 7 |
USE [master] GO CREATE LOGIN [brent_readonly] WITH PASSWORD=N'changeme', DEFAULT_DATABASE=[tempdb], CHECK_EXPIRATION=ON, CHECK_POLICY=ON GO GRANT VIEW SERVER STATE TO [brent_readonly]; GO |
Change brent_readonly to be whatever username you want: typically your regular username, but append _readonly to it so you know that it’s your less-privileged account. Note that you have to change “brent_readonly” in two places.
Change the password from “changeme” to whatever you want.
If you get an error that says:
|
1 2 3 4 |
Msg 15118, Level 16, State 1, Line 3 Password validation failed. The password does not meet the operating system policy requirements because it is not complex enough. |
Then you need to use a more complex password.
Finally, let’s make sure the new login can’t write anything in your existing databases. Copy/paste this in, change brent_readonly to your login name (note that it’s in 3 places), and run it:
|
1 2 3 |
sp_msforeachdb 'USE [?]; CREATE USER [brent_readonly] FOR LOGIN [brent_readonly]; ALTER ROLE [db_denydatawriter] ADD MEMBER [brent_readonly];' |
That only affects the databases you have in place today. If someone restores a database into this environment, you’ll be able to write to it. If you’ve got some time and you’re willing to roll up your sleeves a little, here’s how to deny writes in all new databases long term.
4. Learn to switch back & forth between accounts.
Now that you have a low-privileged account, you’re going to want to use this by default when you connect into SQL Servers with Management Studio. This helps prevent you accidentally causing a resume-generating event.
Close SSMS, and reopen it again. This time, at the connection dialog:
Choose SQL Server authentication because we created a new SQL login, and then type in your low-privileged username and password. Click Connect, and you’re now working a little more safely, without the superpowers of your regular domain login.
If you do need to switch over to your regular high-permission account, you could close SSMS entirely and reopen it, but there’s an easier way. To change accounts for the current query window, click the change-connection button at the top right. It looks like a plug/unplug button:
That’ll give you the connect-to-server dialog box. Note that the login change only affects the currently open query window: that’s the safest way to minimize the damage. Then, as soon as you’re done doing high-permissions stuff, close that window, and you’re back to your regular low-permission stuff.
Next steps for learning
Now that you have an account that’s safe to use for learning, here are a few next steps of tools to explore on how to get to know your SQL Server better.
- How I Configure SQL Server Management Studio – my setup tips
- sp_Blitz – totally free health check script that warns you about dangerous things in your SQL Server that may present problems later
- How to do a health check – my free process for writing up a SQL Server’s health and performance issues
20 Comments. Leave new
Brent, I love the idea, but noticed you took the easy and pretty much the only usable way out using SQL Logins. Please try to respond without pummeling me of demeaning me as this is a serious post. When you live in a more secure environment, SQL security is not usually going to pass your own DBA or your security department’s imposed CIS standards. Microsoft has yet to make a way (in which I’m aware) which allows you to impersonate/log in as another AD user on premise, ex: a sysadmin level account for managing more important things on a server without loss of functionality in SSMS. Yes you can run elevate login from command line as another user or do a runas, but you lose the most important functionality of code snippets in your Template Browser where I store mountains of code snips as the environment gets switched to another user profile, which means for me if I runas another user, this function is useless unless I keep two copies open — a pain! If anyone has a way to login via 2 different AD users on premise smoothly without Azure login modules please share. I would be deeply in your debt!
John – there’s just only so far I can go in any one blog post. Hope that’s fair.
This doesn’t sound like an SSMS issue as we can’t be logged in to a Windows machine with two accounts at the same time either – which is how AD accounts work with SQL Server (kerberos tokens from the Windows session and whatnot).
Not sure how to get what you want, but like Brent said maybe there are some creative people on SE.
John, you can execute the SSMS executable using Run as a Different User. This will let you login with a different set of AD credentials on SSMS.
Certainly, but does that mean there is no answer?
Sorry for not replying to your post!
It means I wish I could do free consulting for everyone in blog post comments, but when you’ve got questions that aren’t the point of the blog post, your best bet is to go to a Q&A site like https://dba.stackexchange.com.
We also struggle a bit with Windows Auth limitations, in particular when using a Bastion Host (with SSMS installed locally) to connect to multiple backend MSSQL instances. Applying various combinations of opening shells under different users (then starting SSMS from the shell) and more direct options like runas or “Run as different user”, we can get by.
SQL Server Templates; default location:
C:\Users\username\AppData\Roaming\Microsoft\SQL Server Management Studio\xx.0\Templates\Sql\
I have not tried to use a shared repository for templates; there are few hacks on offer. The simplest I can think of, is to create a symbolic link (under each user’s profile) which points to shared resource:
# Soft Symbolic Link
New-Item -ItemType SymbolicLink -Name “testSymbolicLink” -Path “C:\Users\username\AppData\Roaming\Microsoft\SQL Server Management Studio\14.0\Templates\Sql” -Target “C:\temp”
# Hard Link (Junction)
New-Item -ItemType Junction -Name “testHardLink” -Path “C:\Users\username\AppData\Roaming\Microsoft\SQL Server Management Studio\14.0\Templates\Sql” -Target “C:\temp”
I have a silly question:
If I have several windows open, can I color code the title bar so that Admin/raised privileges accounts are maybe red and the others are blue? I wouldn’t want to run potentially dangerous code on in an admin account without being fully aware of it.
Pieter there’s a MSSQLTips Article that shows you how to color code the status bar on connection. Never noticed that setting on connecting SQL Server but it makes a lot of sense to me!
https://www.mssqltips.com/sqlservertip/1871/customizing-the-status-bar-for-each-sql-server-ssms-connection/
“You wouldn’t remote desktop into the SQL Server and start playing Fortnite, now, would you? Don’t answer that. You probably would. You’re the kind of person who reads this blog, after all, and I … let’s just stop there. Install SSMS on your desktop.”
Well, of course I wouldn’t play Fortnite on the DB server – the video card is horrible. Now, the newer application server…
I mean, of course I wouldn’t play a game on a server! Perish the thought!
(No, of course I haven’t… But I know people that have.)
“Over time, you’ll learn that running SSMS (as with any other app) on the SQL Server itself will slow it down”
Um, I’m not sure it does.
Sure it does IF you run in – but what about if you install it but use your desktop SSMS for queries and ONLY use Server SSMS if there is an issue. Does it slow down the server when it is not being used?
I don’t think there are any services that run specifically for SSMS only.
SSMS runs a check for updates, but again only after its been run.
There will be disk space used and registry entries, but these shouldn’t impact the speed of the server (if that fills up your HDD and slows it down then you have bigger issues!)
There have been times when using the version of SSMS on the server, which is lower then the desktop version has been a workaround for an SSMS bug, so there can be tangible advantages having it on there at the same version as the database engine.
The resources consumed by SSMS should be very low in comparison to the SQL server in any case, and if it is only being used occasionally then there should be no additional impact on a well sized server.
Geoff – the key word there is “running.” If you run SSMS on the server, that means you’re remote desktopped in and you’re executing it.
I’ve had a surprising amount of consulting engagements where multiple people would Remote Desktop into SQL Server at the same time to “check on some issues” – and then leave SSMS up and running, or leave a long query running, or open a browser from the server to Google an issue, etc.
I’m not okay with that, and I won’t budge on that issue. We may need to agree to disagree. Thanks for the comment though!
Hi Brent,
No we agree leaving it running is just plain wrong!
Thanks for the comment & videos etc. you provide a great service!
RunAs SSMS.cmd
@echo off
echo ***********************************
echo *** Run SSMS as domain user ***
echo ***********************************
echo.
set /P user=”Type the domain\username: ”
C:\Windows\System32\runas.exe /netonly /user:%user% “C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe”
pause
And if you need to use a local user account then use .\username
As a number of people have mentioned using the Runas.exe command line program. An alternative is to use the Microsoft Sysinternals wrapper called ShellRunAs.exe which puts the Runas command in the right click menu on Explorer. That way, you can right click on SSMS on your desktop, choose ShellRunAs and type in different windows credentials. Great if you need to walk up to users desk and use your windows credentials to run SSMS to look at something. Better if you need to cross domains and use that domain login.
https://docs.microsoft.com/en-us/sysinternals/downloads/shellrunas
You can achieve the same result with a SHIFT-RightClick
i.e. hold the SHIFT key down whilst performing a right-click on the SSMS icon; the additional option “Run as a different user” will magically appear.
For those of us still on SQL Server 2008, note that Brent’s use of the ALTER ROLE statement isn’t going to be an option.
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql?view=sql-server-ver15
Correct, SQL Server 2008 hasn’t been supported in years. My advice isn’t relevant to SQL Server 6.5 either. 😉