Grant sysadmin permissions to windows login when SQL Server SA login password is not known

Here are the detailed steps:

1: Start SQL Server in Single User Mode Open SQL Server Configuration Manager.

 
Stop the SQL Server Instance you need to recover

                                      |  
Right-click on the SQL Server Instance and select Properties.

                                      |  
Click on the Advanced tab, and add -m or -f; to the beginning of Startup parameters.

                                      |  
Click OK and start the instance.

2: Add an existing login or a newly created one to the sysadmin server role

After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility using Windows authentication. You can use Transact-SQL commands such as "sp_addsrvrolemember" to add an existing login or a newly created one to the sysadmin server role.


Open an elevated command prompt and enter the command:

SQLCMD -S myServer\instanceName

Replace myServer\instanceName with the name of the computer and the instance of SQL Server that you want to connect to.
At the next prompts, enter the following commands to create windows login for sysadmin role:

CREATE LOGIN [US\TEST] FROM WINDOWS
go
ALTER SERVER ROLE sysadmin ADD MEMBER [US\TEST] 

(Grant sysadmin role for newly created login or existing login)

go
quit

 
Stop the SQL Server instance.

                          |  
Remove the -m option from the Start parameters field, 

                          |

and then start the SQL Server service.


At this point you should be able to login to SQL Server with sysadmin privileges and reset the SA login password.

SQL Server Services stopped after applying SQL Server Cumulative/Security update

 SQL Server Services stopped after applying SQL Server Cumulative/Security update due to following error:

1. Cannot find the user 'ModuleSigner', because it does not exist or you do not have permission.
2. Cannot find the login '##MS_SSISServerCleanupJobLogin##', because it does not exist or you do not have permission.

Note: Above error is one of the example.  Analyst may receive similar error for other missing security objects.

 

Whenever we have such upgrade script (SQL Server Cumulative\Security updates) failure issue and SQL Server instance is not getting started,

To find the root cause of the issue, go to the SQL Server installation Error location

Example: C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\

If you found below error

1. Cannot find the user 'ModuleSigner', because it does not exist or you do not have permission.
2. Cannot find the login '##MS_SSISServerCleanupJobLogin##', because it does not exist or you do not have permission.

We need to use trace flag 902 to start SQL which would bypass script upgrade mode. This would allow us the find the cause and fix it. So, here are the steps to fix missing user 'ModuleSigner' and login '##MS_SSISServerCleanupJobLogin##'

SQL instance needs to be rebooted and after that SQL Server Service does not start. 

As I mentioned earlier, first we started SQL with trace flag 902. I started SQL using trace flag 902 as below via command prompt.

Step 1: NET START MSSQLSERVER /T902

For named instance, we need to use below (replace instance name based on your environment)

NET START MSSQL$INSTANCENAME /T902

Step 2: As soon as SQL Server was started, you will able to connect because the upgrade installation didn’t run. Here is the T-SQL script which creates missing logins '##MS_SSISServerCleanupJobLogin##' and users 'ModuleSigner'.

USE [SSISDB]

CREATE USER [ModuleSigner] FOR CERTIFICATE [MS_SQLISSigningCertificate]

GO

USE [master]  
GO  
CREATE LOGIN [##MS_SSISServerCleanupJobLogin##] WITH PASSWORD='Pa$$w0rd', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF  
GO  
GRANT VIEW SERVER STATE TO ##MS_SSISServerCleanupJobLogin##
GO
USE [SSISDB]  
GO
CREATE USER [##MS_SSISServerCleanupJobUser##] FOR LOGIN [##MS_SSISServerCleanupJobLogin##] WITH DEFAULT_SCHEMA=[dbo]
GO

Step 3: After creating the login\user, stopped SQL Service using SQL Server Configuration Manager. We can also do it via command prompt using below command. Below is for the default instance.

NET STOP MSSQLSERVER

If you are dealing with named instance, then below is the command ((replace InstanceName based on your environment)

NET START MSSQL$INSTANCENAME

Step 4: Then start SQL normally (without trace flag) using SQL Server Configuration Manager.

NET START MSSQLSERVER

Step 5: Verify the SQL instance and install the latest Cumulative\Security update for the instance