Local SQL Server installed by other user account, how to access from new user account

Question

1. • SQL Server was installed on the local PC by User1.
2. • User1 has now left the company and a new account on the PC was created called User2.
3. • User2 cannot access the local SQL Server installation.

Is it possible for User2 to access the local SQL Server without reinstalling?

Here is the error:

Cannot connect to mydatabasename.

Login failed for user 'myuser2name'. (.Net SqlClient Data Provider)

Server Name: mydatabasename

Error Number: 18456

Severity: 14

State: 1

Line Number: 65536

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

Answer 1

The easiest way would be to log in via the "sa" account but I expect you don't have the password to this or it isn't enabled.

If you start the server in single-user mode you will be able to add additional users without reinstalling.

This MSDN page outlines all the steps but in brief they are

1. Stop all services for that instance (including Sql Agent and Integration services) using "Sql Server Configuration Manager"
2. Open up the properties window for the Sql Server instance you need to modify.
3. Add an additional startup parameter "-m"
4. Start the service (make sure you only start the Sql Server service)
5. Start SSMS using "Run as Administrator"
6. Connect to server using windows auth.
7. Add login.
8. Remove -m parameter from service and restart all services stopped above.