How to create database user on secondary node if DB is in readonly mode in SQL Server?

Scenario

How to provide ‘db_dataread’ database role to a SQL login when the database is in AlwaysOn & secondary node DB is in readonly mode?

Introduction

To provide ‘db_dataread’ database role to a SQL login when the database is in AlwaysOn & secondary node DB is in READONLY mode we have to apply a trick. In Active-Passive  AlwaysOn primary node/replica will be active and secondary node/replica will be in READONLY mode. In this scenario, we can create a login on the primary node/replica but could not create a user on the READONLY mode database in secondary.

When we need to provide access to users on a secondary node/replica database, we need to create the Login with the same SID as in primary node/replica and provide access on the primary server which can replicate the same to secondary.

Creating a login on the primary node

Create a new login on the primary node/replica, then create a new user for the login & then provide ‘db_dataread’ database role to the login. Use below TSQL script or same can be done with GUI SSMS also.

Creating ‘sp_help_revlogin’

Create ‘sp_help_revlogin’ on the primary node by using the Microsoft script. to transfer the logins and passwords between different instances of Microsoft SQL Server stored procedure ‘sp_help_revlogin’ will be used. Please follow the URL for the guidance – https://support.microsoft.com/en-us/kb/918992

On primary node/replica, start SQL Server Management Studio, and then connect to the instance of SQL Server from which you moved the database. Open a new Query Editor window, and then run the above-mentioned script. This script creates two stored procedures in the master database. The procedures are named sp_hexadecimal and sp_help_revlogin.

Run ‘sp_help_revlogin’

Run the following statement in the same or a new query window to execute ‘sp_help_revlogin’

The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

Create the login on secondary node/replica

logon to the destination server (Secondary node). On Secondary node, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you need to create the login. Copy the individual script for that particular login which was generated by ‘sp_help_revlogin’ stored procedure. Here we will use ‘Login02’ script.

Open a new Query Editor window, and then run the above script. Below messages will display if the login successfully created.

Check both SID on both logins

To check both SID on both primary & secondary node/replica we may use below script.

Below is the screenshot of the primary node

Below is the screenshot of the secondary node

So, both the SID are the same, as we required.

As per our requirement, we may delete primary login & user if required. Secondary login will work !!

Idea Source: http://j.gs/DFpt


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *