Recently in one of my interaction with a DBA inside a big financial organization, I was surprised to see that the administrator was logging into a desktop / server using a different credential. What baffled me was the fact that he was logging off. I immediately asked, why are you doing this? He said, since he was using windows authentication, which was different from his normal credentials that for DB related activities he used to create a different session. After a bit of research, I found this to be a common practice in a number of organizations.
Many companies provide two different windows account to any Administrator. One “regular” account (example SQLAuthority\Pinal) is used for general work like emails, login to laptop etc. And another “admin” account (example SQLAuthority\adm_pinal) for administrative level tasks to be done on the server. The regular account would have no access to SQL Servers, but the admin – level account would. To make security stronger, the companies use a Windows account to connect to SQL Server.
This makes thing little interesting. If one has to connect to SQL Server from the laptop where the DBA has logged in with regular account, he/she has to do “Right Click” with Shift key pressed and then use “Run as Different user” to use the admin account.
Pressing shift key is important otherwise we would not see that option. After choosing that, it would pop-up with user name and password window. If you notice, you would notice ssms.exe path which is used.
Another way, which I prefer is using Run as command. The program runas.exe allows to let us tell Windows to run a program using a different user’s current network environment instead of the local environment. The full details and switches of the run as the program can be found in TechNet article.
I would normally create shortcut on desktop. Right Click and choose a new shortcut as shown below
and give location/parameter as below
C:\Windows\System32\runas.exe /noprofile /env /user:SQLAuthority\adm_Pinal “C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe”
You need to change two things, user name and the correct path for SSMS.exe
Once it’s saved, you can double click and provide password on the command prompt. I have given a shortcut name as Admin SSMS in below screenshot. As soon as I double click, I see below
One credential is provided, SSMS would open with that windows account.
I must conclude by saying that if you are using SQL Authentication then you do not need to do this because Windows credentials are not passed to SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
22 Comments. Leave new
Thank you for the useful info.
Contractors often end up working across multiple domains as well.
If you need to connect SSMS across domains you can add the /netonly flag to the runas command to allow SSMS to work with a totally different domain.
Dave–I use the template explorer to build a lot of custom code. This seems to have limitations when I log in as another user. Example: if I try to search for a template based on the name I get the error from ssms.exe “Windows cannot access the specified device, path, or file. You may not have the appropriate permissions to access the item.” I have validated that the user is an admin on the box and has access to the path where the templates exist. In fact I can create new folders and templates without an issue. Do yo know of any work araound?
I use the same authentication to log into SQL server, how can I do the same for SQL server Data Tools?
The “runas /user:” option no longer works with SSMS 2016. You get this error “740: The requested operation requires elevation.”. If someone has a workaround for this please share it.
Dave, were you able to run management studio for sql 2016 as a different user?
Yes, It does. Chuck has given more suggestions in below comment.
It works for me. IIRC I had to
1) set ssms.exe to always run as administrator “right click, properties, compatibility tab, change settings for all users”.
2) add SSMS’s location as the first entry in my path
3) run it like this – runas.exe /user:domain\user /savecred “cmd /c \”start ssms.exe\””
Thanks @Chuck
There’s probably a better way than putting SSMS’s location first in the path but frankly I couldn’t figure out all the escaping of quotes needed to make it work and changing the path was simpler.
great tip Thanks!
Thanks @cspell
Thanks For Info but i want to do these in Webconfig Connection String is it possible ?
I guess you are looking for application pools where you can run a pool under given identity.
I’m always happy when my search results show a link to Dave’s blog. I always learn something in addition to whatever problem I am trying to solve. And the articles are so clearly written. Magnificent. Keep up the great work and thank you very much.
Thanks for those kind words rds3wave.
Hi Guys, when prompted for the password, I cannot type it in. Almost like it’s read-only. Any ideas?
Use “Other User” and try.
what would be the connection string if you are trying to access a sql db using a different windows user than the one already logged in.
logged in user: abc
connecting to a db server: as a different user “xyz”
in this case what would be the connection string ?
Pleas share your thoughts and would be of great help.
A connection was successsfully established with the server, but then an error occured during the login process. ( provider: shared memory provider, error: 0 – no process is on the other end of the pipe . microsoft sql server ERROR 233 9:54 PM
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE>ssms.exe -U Domain\username -P <> -d databsename -S <>
I am getting the same error as Asish on 17.2. It’s just not working when run as no matter the options.
You are a legend