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 .
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:
- 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.