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

457 views 05:59 2 Comments 27 September 2019

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.

-- 1. Create new Login on primary server
GO
CREATE LOGIN [login02] WITH PASSWORD=N'Welcome@12345'
GO
-- 2. Create new User for Login02
USE [DB_01]
GO
CREATE USER [login02] FOR LOGIN [login02]
GO
-- 3. Providing db_datareader role to Login02
USE [DB_01]
GO
ALTER ROLE [db_datareader] ADD MEMBER [login02]
GO

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’

EXEC 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.

-- Login: login02
CREATE LOGIN [login02] WITH PASSWORD = 0x02006DE19D81E5BB3C8124A624705FAE15FB4A4AACD1BB02AD56531D4B77013EC4277E4BF025BF43F865FB5CBEC25DE440DF386CDE3C46B755022186A1FF5189BFD941DBD5CE HASHED, SID = 0x342240D57FD2D04CAAADB31570601150, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF

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.

select name, sid from syslogins

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

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

Leave a Reply

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