How to give access to Users in Secondary Always-On/Mirror Server

When we need to provide access to users on a secondary database we will be able to create a login but not user on the read-only database.
To address the problem we need to create the Login with same SID and provide access on the primary server which replicates the same to secondary.

This is tested on multiple Always-On environments. Refer to below code for reference!


-- 1. Create new Login on primary server
USE [master]
GO
CREATE LOGIN [UserName] WITH PASSWORD=N'myPassword', DEFAULT_DATABASE=[DefaultDBName], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

-- 2(a) If we know the password 2(b) can be ignored
-- 2(b) Generate script to create new Login in Secondary servers wiht same SID and password
EXEC sp_help_revlogin
-- https://support.microsoft.com/en-us/kb/918992



-- EXECUTE on Secondary Server 

-- 2(a)Create new login on secondary servers
USE [master]
CREATE LOGIN [UserName] WITH PASSWORD = 'Password', SID = 0x406C68A622630046808714E9F326E4F4, DEFAULT_DATABASE = [DefaultDBName], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

-- 2(b)Create new login on secondary servers
USE [master]
CREATE LOGIN [UserName] WITH PASSWORD = 0x02003F764869C954D8CEB90C667951D8A34D97E2B6806D557B25815D23D8261AAE181A02FCD59EFFFB3AE88485A5B8EBD04DA1B45195A48506A0A6ACD3F342676859D2E4A2FA HASHED, SID = 0x406C68A622630046808714E9F326E4F4, DEFAULT_DATABASE = [DefaultDBName], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF


-- 3. Primary Server 
USE [DefaultDBName]
GO
CREATE USER [UserName] FOR LOGIN [UserName] WITH DEFAULT_SCHEMA=[dbo]
GO
use [DefaultDBName]
GO
GRANT EXECUTE ON [mySchema].[ProcedureName] TO [UserName]
GO


--
--

Advertisements
This entry was posted in High Availability, SQL, SQL Query, SQL Server, Uncategorized and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s