June 7, 2022 at 6:33 pm
Hi,
Using SQL2014 with HA. Cant figure out how to grant a new developer DB access to secondary node only?
I read (and tried) about creating user on primary and then using sid from primary on the secondary. But that only seems to work with SQL auth not windows auth.
Is it possible to have new user on secondary only with windows auth?
Could I use a login trigger to stop access to primary only?
June 7, 2022 at 9:52 pm
Using Windows Auth - you don't need to create a login on the primary node, only the secondary node. Then - create the user in the database for that windows account and define the appropriate permissions.
The new user will be replicated to the secondary and since the Windows SID is the same it will tie to the login created on the secondary and that user will have access to the secondary.
Alternatively, create the login/user on the primary - create the login on the secondary, disable or delete the login on the primary.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 8, 2022 at 12:03 pm
HI Jeffery
I feel pretty dumb for not trying the disable. I did remove the DB's from the user on the primary and noticed that then they were removed from secondary. But I never thought about a simple disable on primary.
Thanks!
June 8, 2022 at 6:45 pm
HI Jeffery
I feel pretty dumb for not trying the disable. I did remove the DB's from the user on the primary and noticed that then they were removed from secondary. But I never thought about a simple disable on primary.
Thanks!
If that secondary is also a failover secondary - then you would want the login on both instances, and an agent job on each node to enable/disable the login based on which node is currently the primary. If that secondary is only for read only access and not a failover node then you can remove the login from the primary.
You can do the same thing with SQL logins - the only requirement there is that you create the login on the 'second' instance with the same SID that was used on the 'first' instance.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply