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.