Launch SQL Server Management Studio (SSMS) as a different user

Many organisations require that administrators have a separate user account for work on production systems, different to the one they use for day to day work.

Unfortunately, newer versions of SSMS display this when you try to launch (or ‘runas’) SSMS as another user.

Neither option, “Restart under different credentials” or “Cancel the task and return to Microsoft SQL Server Management Studio”, will allow you to run SSMS as another user account.

One option is to turn off UAC (in case you don’t know what that is – it’s “User Access Control” – the box that takes over your screen when you try and do something needing admin rights, e.g. install software)

Whilst there is no shortage of opinions and holy wars on the usefulness of this security feature, I believe that in this age of ‘drive-by website attacks’, turning off security protections puts your machine at greater risk. If you agree & would like a quick fix, read on.

Resolution

To allow you to run SSMS as a different user, you need to make one change to the SSMS shortcut.

First, find SSMS on the start menu, right click on it, and select “Open File Location” to find the application shortcut:

Right click on the “Microsoft SQL Server Management Studio” shortcut, select Properties. Under the compatibility tab, click ‘settings for all users’, & tick “Run this program as an administrator”

Then, you’ll be able to run SSMS as another account.

How do you launch an app using another account?

In order to launch a program as another user, you need to copy the application shortcut to your desktop, or right-click on the shortcut and select “Pin to taskbar”.

Having done this, hold down shift and right click on the SSMS shortcut, and select ‘Run as a different user’

Put in your admin credentials, then you will be able to successfully launch SSMS, using your admin account.

If you can’t see ‘Run as different user’, make sure the shortcut is on your desktop or pinned to your taskbar.  Note, the start menu doesn’t seem to offer this function.  Make sure you have shift held down before you right click on the shortcut.

Advertisements

Chocolatey, a sweet package manager

chocolatey

Coming from a Linux system, Windows software installation appears rather confused and fragmented. On Ubuntu (and other Debian derivatives), we have:

  • apt-get install – to install a piece of software
  • apt-get update – to get the latest software versions available
  • apt-get upgrade – to upgrade every piece of software on the system

Using similar commands, it’s even possible to upgrade the entire operating system (apt-get dist-upgrade)

On Windows, we;

  • find the software from the vendor’s website. Hopefully, we pick the right download.
  • download it & run the setup
  • answer a bunch of questions & “click next” a few times, whilst being careful to avoid any agreements that try to install spyware.
  • wait for the software to nag us about upgrading (sometimes)

Automating these installs comes with a number of challenges, there are multiple install technologies with different ‘switches’, e.g. MSI, NullSoft.

Chocolatey is the beginning of a new way – it starts to bring the simplicity of *nix software installation to Windows. Once it is installed, software can be installed with a simple command, e.g.

  • choco install google-earth (to install Google Earth)
  • choco install jre8 (to install Java Runtime Environment)

These commands download software from a vendor’s website, and silently install without any user intervention, similar to apt-get, yum or rpm.

Chocolatey can also upgrade every piece of software under its management, using a single command:

  • choco upgrade all

Let’s just bask in how awesome that is before we continue.

The freely available online repository has, at the time of writing, 4778 packages available. It is straightforward to create an internal repository – only a web server is required, which can be used to host packages which cannot be publicly redistributed, or to avoid having to trust an external resource for software installs.

The client installation is via a simple script – it’s not quite a simple as perhaps it could be (an MSI would be nice), but it is worth it.

Once installed, the simple command-line syntax makes it ideal for scripting, there are resources for Powershell DSC, Puppet and Chef (others too).

A licensed version provides a number of additional features, such as:

  • create chocolatey packages using a simple GUI
  • take software currently installed on an existing PC and make it managed by chocolatey.

Prevent software from upgrading

In the event a piece of software causes an issue, it can be ‘pinned’, for example, this pins the Git Credential Manager to the current version

This will then not be upgraded until the pin is removed:

What about a GUI?

There is a basic GUI, but Chocolatey’s strength lies in making software installs simple to automate. This simple mechanism makes it straightforward to build an ‘appstore’, or integrate it into an existing business workflow.

Why I think chocolatey is great

  • Chocolatey makes software installs easy, it has a community that keeps common, frequently updated, software such as Google Chrome up to date
  • The simple syntax makes it easy to automate, there is no need to load complex Powershell modules, or connect via PS Remoting to a complex console.
  • It is easy to create packages
  • You can use the public chocolatey packages to get started and there are nearly 5000 installers already available.  Why repackage Chrome, Java, etc yourself?
  • If you want to host your own repository for your own software, the infrastructure behind chocolatey is really simple – it’s just IIS, packages are just files. This can be scaled to deal with larger organisations using existing mechanisms, e.g.
    • The website can be load balanced to provide high availability
    • Packages can be distributed to remote sites using DFS-R (or similiar).
    • A simple workflow can be established to connect some users to an early release source, for testing and QA.
    • It’s simple to introduce basic processes to manage software – e.g. create a scheduled task for choco upgrade all to keep all your software up to date.
  • Using the business edition
    • end users can install software themselves without admin rights (Youtube video)
    • it is even easier to create packages (Youtube: there is a simple GUI too), often it automatically works out the automated switches required to install software.
    • packages can be automatically created from software installed on existing systems (Youtube video)
    • .. and much more
  • Combine it with Boxstarter and automate building your next dev machine!