How to become a sysadmin on a SQLEXPRESS 2008 installation when you are not the original installer, SQL authentication is not enabled but you are a Windows administrator.


I have gotten a new job and I am in that phase where you start slowly to setup your machine, the environment etc.

One of the things I had to do is to set up the database for the project my team works on. The database has to be configured locally on a 2008R2 SQLEXPRESS instance (installed already by an system administrator and obviously under a different Windows account).

Long story short when I attempted to create a database I got access denied. Any other attempt to gain administrative rights over the database server failed. Did I mentioned the account I was logged on is part of the local Administrators? Yes, that too and even though I launched the management studio in admin mode as well, it did not help. It seemed as the database server did not really care that I am a mighty administrator on my machine Open-mouthed smile.

Reading some articles out there I learnt that the MS-SQL 2008 version does not include the Windows administrators in the by now very select and limited group of sysadmins. It seems to be true at least in my case. This means that if the person that installs the database server does not make you specifically a sysadmin you will not be automatically a full privileged user over the database server only because you are a Windows admin.

There is a way you can make yourself a sysadmin in a situation like mine where somebody else installed the server, left you out and you do not have access to the original installer.

You have to launch the database server in “single-user” mode which is when your Windows administrator account can act as a sysadmin and you can add it as a login to the database and make it part of the sysadmins role.

  • Start by stopping the SQL server and close the Management Studio
  • Launch a command prompt as an Administrator.
  • Then launch the SQL Server Configuration Manager; select SQL Server services; right click on the SQL Server (SQLEXPRESS) service and click Properties.
  • Select the Service tab and double click on Binary Path; you should get a drop down containing the command that launches that specific SQL instance:
  • image
  • Copy it and paste it in the previously opened command prompt window; add an extra –m to the parameters: “”c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe” –m –sSQLEXPRESS”;
  • Press Enter and it will look like Linux is about to start. When it stops and one of the last lines logged to the window is saying “SQL Server is now ready for client connection” it means you succeeded and your “single-user” instance is running.
  • Launch the SQL Server Management Studio as an Administrator and do whatever you want because you are now a sysadmin; basically you would like to add your Windows account as a SQL login; add the sysadmin server role to this login;
  • When you are done with it return to the command prompt window and press Ctrl+C and respond Y to shutdown the SQL service.
  • Go back to the Management Studio and start your instance the usual way and test. You should be able to do what you please.

Hope this helps.

Cheers.

Advertisements

3 thoughts on “How to become a sysadmin on a SQLEXPRESS 2008 installation when you are not the original installer, SQL authentication is not enabled but you are a Windows administrator.

  1. There is one issue in this step:
    Launch the SQL Server Management Studio and do whatever you want because you are now a sysadmin; basically you would like to add your Windows account as a SQL login; add the sysadmin server role to this login;

    Launching of SQL Server Management Studio needs to run as Administrator mode or you will get an error: Login failed for user.. Reason: Server is in single user mode. Only one administrator can connect at this time.

    1. I thought it was implied, but your observation is correct.

      I missed it because as soon as I can I set my pinned taskbar icons to launch many of my apps especially administration consoles in Administrator Mode.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s