Link to home
Start Free TrialLog in
Avatar of tschary
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
Avatar of Rich Weissler
Rich Weissler

I assume what is missing is user logons.  A couple ways to resolve the issue... one of which would be to use Contained Databases.  (That said, I haven't met anyone using Contained Databases yet.)

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.
Avatar of tschary

ASKER

Hi Rich, Privileges like db_owner role is missing every alwayson failover  for the user SA authentication. Please provide your inputs
SOLUTION
Avatar of Rich Weissler
Rich Weissler

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tschary

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
Avatar of tschary

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--0xA406F104F9DD7E47B878D36E89E942A8
test2--0x4F7B55F8418491458A1533DA6311E76B
test4--0xF4E66317DD16194FB31DC44390126D4E
test4--0xDDA40B51CA780041ABD7FE61DCF26516


Instance B

test1--0x58316DE47D95094BA41A1712ABD9A64D      
test2--0x5CE9289F5AD5A546832C99EBE94626DC      
test3--0x74DEDB589EDC024580033A407F6A9CD1      
test4--0x244A52011DCD2E44BE8D3A7A8AEEF431
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?
Avatar of tschary

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]
Check if the default database are the same for the Login in both SQL Server instances.
Also check if they have the same UID:
SELECT principal_id 
FROM sys.server_principals
WHERE name = 'LoginName'

Open in new window

Sorry, you need to verify SID and not the principal_id:
SELECT sid
FROM sys.server_principals
WHERE name = 'LoginName'

Open in new window

Avatar of tschary

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.
Remove those logins from the secondary and transfer them following this Microsoft's article. This will guarantee the same SID on both replicas.
Avatar of tschary

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.
Avatar of tschary

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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tschary

ASKER

I tested in verification now and it worked well. Thanks for support.
Avatar of tschary

ASKER

Thanks a lot.