Fix SQL Server Error 15433, Level 16, State 1: Supplied parameter sid is in use
I got Microsoft SQL Server error 15433 during fixing orphaned users on a SQL Server Instance where AlwaysON Availability Group was configured. Our application was facing connectivity issue post AOAG failover and not able to connect to database whereas everything was working fine from database end. Databases were successfully transitioning their roles during failover.
Microsoft SQL Server Error 15433, Level 16, State 1: Supplied parameter sid is in use
I have an AOAG configuration having two replicas in my environment which is set for Automatic failover. Last week, we had High Availability testing for all applications. Issue occurred when application team tried to connect to database server after failover. They were failed to connect to the secondary replica using Listener after failover whereas applications were working fine from primary replica. Issue came because SQL login which was used by application has different sid on secondary replica than the primary replica.
During fixing above issue, we came across an error about which we will be discussing in this post. One of the engineer has accidently created a wrong login with the same SID that we were about to use to create the application login to fix above issue. When we have tried to create application login we get below error.
Here i will explain step by step method to fix this SQL Server Error 15433.
Solution
Here i will reproduce this issue and then fix it in a step by step manner. So, final objective is to create a login with same name, password & sid on secondary replica to sync the login details of associated application. Let us first check the login sid of identified login on primary replica. Run below code to get it.
SELECT name, SID, createdate from syslogins
WHERE name='xyz'
Copy the SID of this login from above screen for further uses. Now connect to secondary replica and run below command to create same login with same password & sid. You can remove Check_Policy option if your login has password policy applied.
CREATE Login xyz WITH password = 'techy@z@123', Check_Policy=off, SID = 0x03981anjkhdvehh73964jbdbj783
Here we got the issue because someone has already created another login with same SID. Now our next step is to check that login which was created with this SID. Run below command on secondary replica to get this done.
SELECT name, SID, createdate from sysloginswhere SID = 0x0398manjkhdvehh73964jbdbj783
We can see the login name which was created under given SID. We have to created application login with same SID to establish application connectivity to database after failover so we will drop this Login and recreate application login with this SID. Later we can create deleted login which will have different SID. If not needed you should not create unwanted logins on SQL Server. Run below command to drop this login.
--Change login name with login. DROP Login login
SSMS way to drop a login is given in below steps:
- Connect to target SQL Server Instance.
- Expand the security folder.
- Expand the Logins folder.
- Right click and choose delete on the identified login which needs to be deleted.
- Click on Ok button of the login deletion window.
You might get multiple dependency errors during dropping this login because there is possibility this login might own few objects, agent jobs or databases on this instance if it was created long before. You can read below articles to fix such kind of issues if you will face any.
- How to fix Error 15174: Login owns one or more databases.
- How to fix Error 15173: Revoke the permissions before dropping the login.
- Fixing error 15138 & Error 3729: DROP login failed
- Error 15141: The server principal owns one or more endpoint(s) and cannot be dropped
- How to fix error 15170: Login owns one or more SQL Agent Jobs.
Now once this login will be dropped, you will be able to create your application login with required SID. You can see that in below screen.
CREATE Login xyz WITH password = 'techy@z@123', Check_Policy=off, SID = 0x03981anjkhdvehh73964jbdbj783
We can see command executed successfully. Now issue is fixed here.
I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.
- SQL Server Update Statistics Best Practices - April 15, 2020
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
