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 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- 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’
|
1 |
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.
|
1 2 |
-- 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.
|
1 |
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
Like!! Great article post.Really thank you! Really Cool.
Thanks! I didn’t realize I would need to do this with SQL Logins as opposed to Windows logins.