April 22, 2011 by Mike Hillwig
Like most companies, mine has to contend with some form of regulatory compliance. In our particular case, it’s Sarbanes-Oxyley 404, better known as SOX. We have some network policies to enforce compliance with the implementation of SOX. One of those policies is that people with elevated access to key systems have a seperate account for administrative tasks. This means I have two Active Directory accounts, usermike and dbamike. Oh, and of course, I’m not supposed to be logged into my regular workstations with my administrative account. Just to make life a little more interesting, we have a policy that says we don’t do administrative tasks with service accounts, such as SA.
usermike doesn’t have access to crap, especially database servers. Let me put it this way, Claire in Marketing has more access on the network than usermike.
As a DBA, this situation can be hell. When I first started, I was launching a remote desktop session to my SQL servers in order to run SQL Management Studio. The only thing worse than running a remote desktop session to a SQL server is running SSMS in that remote desktop session. When we bought a new SQL tool, I realized that keeping RDC sessions open all day just wasn’t the solution. I went looking for a better solution.
One day, I stumbled accross the RUNAS command. Basically, from a command line, I can launch an application and state the user that should be used to run the application. In my case, dbamike runs the application. It will prompt me for that user’s password, and off we go. It looks something like this for SSMS.
runas /user:domain\dbamike "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
After putting this in a batch file on the desktop, life became a lot simpler. My productivity soared.
Like most things, there is a gotcha. When I attempt to save a file, it will save it in dbamike‘s My Documents folder. That’s because the application is running as dbamike. It also means I can’t open files directly from usermike‘s Outlook mailbox. I have to save it to c:\mike first. It’s an extra step, but the trade-off is wel worth it.