0

Just installed SQL Server Management Studio with Tools including LocalDB. Went through entire setup without ever seeing an option to set the sa password nor setup any other users/logins with sysadmin rights. So now I can't actually do anything in SSMS. Permission is denied to create new database, change the sa password, etc. Pretty useless until I can either log in as sa or setup my user with sysadmin rights. What am I missing here?

jmease
  • 2,507
  • 5
  • 49
  • 89
  • Did you install an actual SQL Server engine, or did you perhaps choose only LocalDB? And are you trying to connect to the local Express instance, or to LocalDB? – Aaron Bertrand Aug 28 '12 at 19:24
  • I installed SQL Server 2012 Express Edition with Tools (x64). During setup, there were two items checked to install. I forget the first one but the second was LocalDB. When I go into SSMS, I connect to .\SQLEXPRESS using windows authentication. – jmease Aug 28 '12 at 19:27

1 Answers1

2

During setup you should have seen a screen that offered you to choose mixed authentication for the database engine and also add Windows users to the sysadmin server role. Here's what it looked like:

enter image description here

Chances are you blew past it clicking Next > Next > Next. It's ok, we've all done that.

The easiest fix, I think, is to connect using SSMS via PSExec.exe. This will allow you to connect as NT AUTHORITY\SYSTEM, which has inherent sysadmin rights to SQL Server. You launch it with these options:

PsExec -s -i "C:\...path to ssms...\Ssms.exe"

This will run SSMS as NT AUTHORITY\SYSTEM using Windows Auth. You will be prompted to connect to a server, just enter .\SQLEXPRESS (make sure the service is running):

enter image description here

Then you can right-click your instance and change the authentication mode to mixed (Properties > Security), reset the sa password under Security > Logins, and add your Windows login as a sysadmin.

You can also see this answer if that doesn't get you everything:

https://stackoverflow.com/a/12164306/61305

Community
  • 1
  • 1
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thanks. I must not be installing the correct thing then because I definitely never saw that window. I specifically combed through all windows during installation as this is my 2nd time installing and I figured I must've missed something the first time around. I installed SQL Server 2012 Express Edition with Tools (x64) from TechNet. – jmease Aug 28 '12 at 19:45
  • And you're sure you selected Database Engine on the Feature Selection screen? It's not checked by default. – Aaron Bertrand Aug 28 '12 at 19:46
  • Yes. Database Engine, Server = .\SQLEXPRESS. But the user name is grayed out and it is currently my username and not NT AUTHORITY\SYSTEM. I have confirmed in SQL Server Configuration Manager that SQLSERVER(SQLEXPRESS) is running and set to logon as NT AUTHORITY/NETWORK SERVICE. But it still tries to connect as me. – jmease Aug 28 '12 at 19:50
  • Sorry, misunderstood your question I think. The only two options I had during feature selection were Management Tools Basic and LocalDB. Both of which I checked. – jmease Aug 28 '12 at 19:51
  • You need to download PsExec in order to connect to the engine as NT AUTHORITY\SYSTEM. I provided a link in my answer. – Aaron Bertrand Aug 28 '12 at 19:54
  • And if you didn't see [these options](http://i.stack.imgur.com/l9ANd.png) during setup, you're either using the wrong executable, or you chose the wrong option in the installation center. – Aaron Bertrand Aug 28 '12 at 19:57