There are times when you’ll need to use Windows authentication to get to SQL Server Management Studio, but the Windows login is not the one you’re currently logged into your computer with. Perhaps you’re a consultant or IT worker and you have logins in several different domains. Or if you’re working with an application or enterprise system like SharePoint, you may be using several different service accounts to do the configuration, and one or more of those AD accounts may have SQL permissions that your personal login does not. It’s crazy to be working “blind,” so you need to get into SQL and see those databases or query them sometimes.
You can try to log into a computer using those accounts, but the quick and easy way is to just use a command line run as that user to open SSMS. If you use this once, then take the extra ten seconds and make yourself a shortcut, because I guarantee it will come up more than once. I have three different shortcuts on my desktop, two for my own account on other domains, and one for the SharePoint install service on my own domain. Note – the below commands are what I use to get to my own SSMS for SQL Server 2008 R2. Obviously check and adjust to the path of your own SQL installation.
Logging into SQL as an AD account in a different domain:
C:\Windows\System32\runas.exe /netonly /user:[domain name]\[username] "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
Logging into SQL as an AD account in your own domain:
C:\Windows\System32\runas.exe /user:[domain name]\[username] "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
(So in other words, remove the /netonly if you’re using a Windows account in your own domain).
In either case, you’ll have a cmd.exe window pop up and you’ll be prompted,
Enter the password for [your domain here]\[the username]:
Simply enter the password for the account you’re using and hit Enter.
At that point, if you’ve entered the correct password, SQL Server Management Studio should open. Select the server you want and be sure to select Windows Authentication. Now you should be logged into SQL using the AD account that has the SQL permissions you need to do your task.