tschary
asked on
Missing Privileges after every failover of SQL Server 2014 Always-on
Hi Experts,
I have observed that after every failover ,the SQL Server Authentication user privileges are missing in SQL Server 2014 Always-on Servers. I am adding again the privileges. Please guide me how to fix this.
Thanks,
Sreenivasa
I have observed that after every failover ,the SQL Server Authentication user privileges are missing in SQL Server 2014 Always-on Servers. I am adding again the privileges. Please guide me how to fix this.
Thanks,
Sreenivasa
ASKER
Hi Rich, Privileges like db_owner role is missing every alwayson failover for the user SA authentication. Please provide your inputs
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes.Its SA. user account name is same but copid from one to another . user account is exist on both instances but database role is missing. I wil go through the link and revert. thanks
ASKER
Hi Rich,
issue is SA logins exist on both SQL Server 2014 Alwayson instances but privileges like database role db_owner is missing when ever fail-over occurred. I have seen the SID and they are different. Please see the below and suggest.
test1--0xA406F104F9DD7E47B 878D36E89E 942A8
test2--0x4F7B55F8418491458 A1533DA631 1E76B
test4--0xF4E66317DD16194FB 31DC443901 26D4E
test4--0xDDA40B51CA780041A BD7FE61DCF 26516
Instance B
test1--0x58316DE47D95094BA 41A1712ABD 9A64D
test2--0x5CE9289F5AD5A5468 32C99EBE94 626DC
test3--0x74DEDB589EDC02458 0033A407F6 A9CD1
test4--0x244A52011DCD2E44B E8D3A7A8AE EF431
issue is SA logins exist on both SQL Server 2014 Alwayson instances but privileges like database role db_owner is missing when ever fail-over occurred. I have seen the SID and they are different. Please see the below and suggest.
test1--0xA406F104F9DD7E47B
test2--0x4F7B55F8418491458
test4--0xF4E66317DD16194FB
test4--0xDDA40B51CA780041A
Instance B
test1--0x58316DE47D95094BA
test2--0x5CE9289F5AD5A5468
test3--0x74DEDB589EDC02458
test4--0x244A52011DCD2E44B
SA doesn't need db_owner in databases. Being SA is implicit that it owns any database in the SQL instance.
Are you getting any error because of this?
Are you getting any error because of this?
ASKER
Hi Vitor,
user is SQL Server authentication account. we have created and provided only db_owner privileges to the database and not a sysadmin role. when a fail-over occurred we are losing privileges on another instance. Please find below the error and suggest me.
Message
Error: 18456, Severity: 14, State: 40.
Login failed for user 'test1'. Reason: Failed to open the database 'xyz' specified in the login properties. [CLIENT: xx.xx.xx.xx]
user is SQL Server authentication account. we have created and provided only db_owner privileges to the database and not a sysadmin role. when a fail-over occurred we are losing privileges on another instance. Please find below the error and suggest me.
Message
Error: 18456, Severity: 14, State: 40.
Login failed for user 'test1'. Reason: Failed to open the database 'xyz' specified in the login properties. [CLIENT: xx.xx.xx.xx]
Check if the default database are the same for the Login in both SQL Server instances.
Also check if they have the same UID:
Also check if they have the same UID:
SELECT principal_id
FROM sys.server_principals
WHERE name = 'LoginName'
Sorry, you need to verify SID and not the principal_id:
SELECT sid
FROM sys.server_principals
WHERE name = 'LoginName'
ASKER
Yes you are right, I have observed for other SA users and they have same SID on both the instances.So we did not get privileges issues after failover. And we are getting few users which has different SID on both instanceS.
Shall I copy the Primary replica User login script and execute in the secondary. I think secondary will not allow changes as it is readonly databases.
Please guide me how to change SID in secondary replica and keep both the instances has same SID.
Shall I copy the Primary replica User login script and execute in the secondary. I think secondary will not allow changes as it is readonly databases.
Please guide me how to change SID in secondary replica and keep both the instances has same SID.
Remove those logins from the secondary and transfer them following this Microsoft's article. This will guarantee the same SID on both replicas.
ASKER
Thanks Vitor, I went though the link . I want it for only 4 users and not for all . it is a critical server and I want to do it for only different SID users. Do we have any more easy method? .. Meanwhile i will try to do it in verification.
No, that's the only and the correct method but you have it all there. Just copy the script and you can add a filter just for those 4 logins.
ASKER
Hi Vitor,
I tried as per your suggestion and created stored procedures. but while running sp_help_revlogin script in secondary replica , it is not allowing and getting error below.
Failed to update database "OP Jobs" because the database is read-only. Error: 3906.
Please help me to resolve.
I tried as per your suggestion and created stored procedures. but while running sp_help_revlogin script in secondary replica , it is not allowing and getting error below.
Failed to update database "OP Jobs" because the database is read-only. Error: 3906.
Please help me to resolve.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tested in verification now and it worked well. Thanks for support.
ASKER
Thanks a lot.
Cheers
Alternately, Jonathan Kehayias has a tool for synchronizing logon objects, which would be an add-in for SSMS.
Should also be possible to automate a solution with an SSIS package.