Forgot the ‘sa’ password and none of the user is having access to the SQL server

Today I got one SQL request saying that they are not able to login into the SQL server 2008 R2 after installing the SQL server in Windows Server 2008 R2.

While talking to them I came to know the below issues:

·         They have installed the SQL server with default instance and it’s a fresh installation and no one have accessed the server yet.

·         They haven’t added any user Windows User id while installing the SQL server.

·         Have disabled the ‘sa’ account and forgot the password for it.

·         It is a SQL server 2008 R2 installed windows SQL server 2008 R2.

Resolution:

Open CMD by run as administrator (Right Click on Command PromptàSelect Run as administrator) as below:

Note : Incase of Windows Server 2003, we may need Windows administrator password.

It will open the cmd prompt à type Sqlcmd –S Server_Name as below and click on ENTER.

Ex: sqlcmd –S ABCDSQL01

 

Type user masteràEnteràgoàEnter as below, it will change the query window to master now.

  

To make sure whether you are the same server , please run SELECT @@SERVERNAME and check as above to confirm.

Type the below command and click on Enter

If you want to add some SQL User id , please follow the below steps:

                                                                                          

                                                                                                                                                      

CREATE LOGIN User_ID with Password=’Some Password’

Ex:  CREATE LOGIN DILIP with Password='Abcd@123'

EXEC sys.sp_addsrvrolemember @loginame = N'DILIP', @rolename = N'sysadmin'

  

If you want to add some Windows User ID, please follow the below steps:

CREATE LOGIN [Domain/userid] with Windows

Ex: CREATE LOGIN [corp\abcd] with windows

EXEC sys.sp_addsrvrolemember @loginame = N'[corp\abcd]', @rolename = N'sysadmin'

                                                                                                                                                                             

Note: @rolename , please specify the permission which ever you need , for example I have given sysadmin (FULL permissions to the user id).

After adding the user id , please try to check your access by login into the SQL server and add the user id which ever you want.

Finally issue is resolved and we are able to access the SQL server with the account.

Please let me know if you are facing any issue even after trying the above steps, I will try to help you to fix the issue.