Hello Friends,

It’s being a many days I haven’t written anything. But there was lot of stuff I was thinking to nail it down as my experience but I was too busy to write it out.

Some days back I faced the issue which was the first time in life and was also weird in it nature. I was working in one environment where my SharePoint got down because of SQL(as sharePoint is connecting to sql with unusable windows account).

• Only Sysadmin user is windows authentication account and the user is not available for long duration(cannot use that user in any case).
• Sql authentication account is not configured.
• I am normal window user with just an only access to SQL(no permission to me).

So, no account is there to access Sql with all permissions. I was in a great mess situation.

While connecting with my normal window user below are the errors which I get at different steps:

1)      Trying to create a new database. Not able to create a database:

2)      Adding myself to Sysadmin role

3)      While trying to enable the SA account.

So a ray of hope was still there to save the SharePoint environment i.e through certain legal tricks you can make servers administrator as a SYS admin (sysadmin) of SQL.

Below are the steps to work on that:

1)      Added my account domain\test1 to server administrator. It can be done through : Go to Run prompt type è lusrmgr.msc and add to group administrators by person who is server admin.

2)      Close sql server management studio.

3)      Stop all the Sql named services from services.msc . If they are more than 1 instance of sql server running on same server you need to stop the services of the right one on which you need the access

4)      Go to Sql server Configuration manager. If they are more than 1 instance of sql server running on same server you need to select the right one on which you need the access.

Note: we are doing all this to bring the Sql server to Single user mode.

5)      Stop the Sql server services if not already stopped as stated before(even reporting service if running): Right-click the instance of SQL Server, and then click Properties.

6)      On the Startup Parameters tab, in the Specify a startup parameter box, type –m; (without space) and then click Add.

7)      Click ok and start the Sql server Service.

Note: Open Sql server management studio. Without connecting to object explorer ,connect with a Query Window using Windows Authentication of domain\test1 (my user for this case). You can do this by : when you open SSMS on pop up of connecting click cancel and then click new query and then connect.

8)      Once you are connected: execute the below command:

===================================================

USE [master]

GO

CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

GO

EXEC master..sp_addsrvrolemember @loginame = N’BUILTIN\Administrators’,

@rolename = N’sysadmin’

GO

===================================================

You are done .Congrats !!

All the administrator of server are now Sysadmin.

You can now add any window account explicitly and make it sys admin and then delete the group “BUILTIN\Administrators” from SSMS security => login.

How to Enable Sql authentication Sa:

1. In Object Explorer, expand Security, expand Logins, right-click Sa, and then click Properties.
2. On the General page, you might have to create and confirm a password for the login.
3. On the Status page, in the Login section, click Enabled, and then click OK.

You are done….

Now:

1)      Your window account has been added to sysadmin.

2)      Sa account has been enabled

Hope I was able to describe the content correctly based on my knowledge and learning.

If you liked this post, do like on Facebook at: https://www.facebook.com/Ashishsharepointblog

Feel free to Rate and provide feedback if you find post useful

Hope this help
Ashi