SQL-ASK For DBAs

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

2023-03-30T22:32:00.000-07:00

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

2023-03-28T09:11:00.001-07:00

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

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]

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

Refresh database from PROD to Dev server in existing Alway-on configuration automatically

2021-07-06T05:09:00.002-07:00

PowerShell Script to copy from PROD server to Dev03 and from Dev03(AG Primary) to Dev04(AG secondary) server.

You can schedule a job in task scheduler to automatic copy file.

# Delete all existing backup from below location

Remove-Item D:\ProdBackup\*.bak

Write-Output "Deleted all old existing backup from Dev03 server : TASK COMPLETED"

# Source and Destination backup file location

$_sourcePath ="\\ABC-PROD01\e$\CopyOnlyBackup"

$_destinationPath = "D:\ProdBackup";

# Pick latest .bak file and Copying file Des to Source and rename

$Datetime =Get-Date

Write-Output ("PROD Latest backup file copying started at " + $Datetime + " ...COPY TASK RUNNING....." )

@(Get-ChildItem $_sourcePath -Filter *.bak | Sort LastWriteTime -Descending)[0] | % { Copy-Item -path $_.FullName -destination $("$_destinationPath\testDB.bak") -force}

$Datetime1 =Get-Date

Write-Output ("Latest backup file copy Completed on Dev03 server and renamed database file name at " + $Datetime1 + "...COPY TASK COMPLETED..." )

################# Copy backup From Dev03 to Dev04 ######################

# Delete all existing backup from below location

Remove-Item \\ABC-DEV04\d$\ProdBackup\*.bak

Write-Output "Deleted all old existing backup from Dev04 server : TASK COMPLETED"

# Source and Destination backup file location

$_sourcePath ="D:\ProdBackup"

$_destinationPath = "\\ABC-DEV04\d$\ProdBackup";

# Pick latest .bak file and Copying file Des to Source and rename

$Datetime =Get-Date

Write-Output ("PROD Latest backup file copying started at " + $Datetime + " ...COPY TASK RUNNING....." )

@(Get-ChildItem $_sourcePath -Filter *.bak | Sort LastWriteTime -Descending)[0] | % { Copy-Item -path $_.FullName -destination $("$_destinationPath\testDB.bak") -force}

$Datetime1 =Get-Date

Write-Output ("Latest backup file copy Completed on Dev server and renamed database file name at " + $Datetime1 + "...COPY TASK COMPLETED..." )

Once Copy done by above Script.

Create below job on AG primary server Dev03.

Note: This will contain total 6 step to Restore DB on Primary and Secondary server and will configure AG between Dev03 and and Dev04 automatically.

USE [msdb]

/****** Object: Job [Monthly_Test_DB_Refresh_From_PROD] Script Date: 7/6/2021 7:45:32 AM ******/

EXEC msdb.dbo.sp_delete_job @job_id=N'8c70a241-323d-48e5-80ce-f97190c07661', @delete_unused_schedule=1

/****** Object: Job [Monthly_Test_DB_Refresh_From_PROD] Script Date: 7/6/2021 7:45:32 AM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 7/6/2021 7:45:32 AM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Monthly_Test_DB_Refresh_From_PROD',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'No description available.',

@category_name=N'[Uncategorized (Local)]',

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [1-Remove AG from Dev Primary] Script Date: 7/6/2021 7:45:32 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1-Remove AG from Dev Primary',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=4,

@on_success_step_id=2,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'CmdExec',

@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\RemoveDBFromAGgroup.sql

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [2-DeleteDBOnSecondary] Script Date: 7/6/2021 7:45:32 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'2-DeleteDBOnSecondary',

@step_id=2,

@cmdexec_success_code=0,

@on_success_action=4,

@on_success_step_id=3,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'CmdExec',

@command=N'sqlcmd -S ABC-DEV04 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\DeleteDBOnSecondary.sql',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [3-DeleteDBOnPrimary] Script Date: 7/6/2021 7:45:32 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'3-DeleteDBOnPrimary',

@step_id=3,

@cmdexec_success_code=0,

@on_success_action=4,

@on_success_step_id=4,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'CmdExec',

@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\DeleteDBOnPrimary.sql',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [4-RestoreDBOnPrimary] Script Date: 7/6/2021 7:45:32 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'4-RestoreDBOnPrimary',

@step_id=4,

@cmdexec_success_code=0,

@on_success_action=4,

@on_success_step_id=5,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'CmdExec',

@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\RestoreDBOnPrimary.sql',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [5-RestoreDBOnSecondary] Script Date: 7/6/2021 7:45:32 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'5-RestoreDBOnSecondary',

@step_id=5,

@cmdexec_success_code=0,

@on_success_action=4,

@on_success_step_id=6,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'CmdExec',

@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\RestoreDBOnSecondary.sql',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [6- Add DB in AG] Script Date: 7/6/2021 7:45:32 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'6- Add DB in AG',

@step_id=6,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'CmdExec',

@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\AddDBInAG.sql',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

Now the below SQL script of code divided in 6 step for your above created SQL Job.

Save each steps of code on Dev03(AG primary server) at D:\ProdBackup\AG_Refresh_SQL_Scripts\*.sql format

Note: File name refer from created job steps.

All 6 *.sql file will access by above job.

Note: Before/while saving the .sql file go to Query --> and SQLCMD option.

-----------File name 1 -Remove DB from AG group-------------------------

:connect ABC-DEV03

USE [master]

/****** Object: AvailabilityDatabase testdb Script Date: 7/1/2021 2:45:39 AM ******/

ALTER AVAILABILITY GROUP [ABC-DV-AWO]

REMOVE DATABASE testdb;

-----------File name 2 Delete DB on Secondary ------------------------------

:connect ABC-DEV04

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'testdb '

USE [master]

/****** Object: Database testdb Script Date: 7/1/2021 2:48:58 AM ******/

DROP DATABASE testdb

----------File name 3 Delete DB on Primary------------------------------

:connect ABC-DEV03

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'testdb '

use testdb

use [master]

USE [master]

ALTER DATABASE testdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

USE [master]

/****** Object: Database testdb Script Date: 7/1/2021 2:51:08 AM ******/

DROP DATABASE testdb

-------File name 4 Restore DB on Secondary -------------------------------

:connect ABC-DEV04

RESTORE DATABASE TestDB

FROM DISK = N'D:\ProdBackup\testdb.bak'

WITH FILE = 1,

MOVE N'testdb' TO N'E:\Data\testdb.mdf',

MOVE N'testdb_log' TO N'L:\DBLogs\testdb.ldf',

NORECOVERY,

NOUNLOAD, REPLACE, STATS = 5

-------File name 5 Restore DB on Primary-------------------------------

:connect ABC-DEV03

RESTORE DATABASE TestDB

FROM DISK = N'D:\ProdBackup\testdb.bak'

WITH FILE = 1,

MOVE N'testdb' TO N'E:\Data\testdb.mdf',

MOVE N'testdb_log' TO N'L:\DBLogs\testdb.ldf',

NOUNLOAD, REPLACE, STATS = 50

---------------File name 6 - add DB in AG -----------------------

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:Connect ABC-DEV03

USE [master]

ALTER AVAILABILITY GROUP [ABC-DV-AWO]

MODIFY REPLICA ON N'ABC-DEV04' WITH (SEEDING_MODE = MANUAL)

USE [master]

ALTER AVAILABILITY GROUP [ABC-DV-AWO]

ADD DATABASE testdb;

:Connect ABC-DEV04

-- Wait for the replica to start communicating

begin try

declare @conn bit

declare @count int

declare @replica_id uniqueidentifier

declare @group_id uniqueidentifier

set @conn = 0

set @count = 30 -- wait for 5 minutes

if (serverproperty('IsHadrEnabled') = 1)

and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)

and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)

begin

select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'ABC-DV-AWO'

select @replica_id = replicas.replica_id from

master.sys.availability_replicas as replicas

where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id

while @conn <> 1 and @count > 0

begin

set @conn = isnull((select connected_state from

master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)

if @conn = 1

begin

-- exit loop when the replica is connected, or if the query cannot find the replica status

break

end

waitfor delay '00:00:10'

set @count = @count - 1

end

end try

begin catch

-- If the wait loop fails, do not stop execution of the alter database statement

end catch

ALTER DATABASE testdb SET HADR AVAILABILITY GROUP = [ABC-DV-AWO];