March 8, 2011 at 3:09 am
DBAs!
I'm about ready to tear my hair out on this one; I have encountered this before, but following the normal troubleshooting steps to solve this has not yielded the necessary outcome... i.e. FIXED!
This occured after deleting a login from SSMS by simply doing a right-click | delete on the login.
I then tried to re-add the login a while later and received this error.
This has occured for Domain Group Logins, Domain User Logins and straight SQL logins (i.e. more than one occurrence).
It has occured for a brand new login not even associated/linked/mapped to a database.
Thinking it was an orphan record of some kind, I tried the following:
1. Checked every database to ensure the entry was not listed under the database's user list
2. Ran as sa: DROP LOGIN [MyDomain\DomainUserA]
*Result: "Cannot drop the login 'MyDomain\DomainUserA', because it does not exist or you do not have permission."
3. Ran as sa against each database: DROP USER [MyDomain\DomainUserA]
*Result: "Cannot drop the user 'PPM-SA\SQL DBAs', because it does not exist or you do not have permission."
4. Ran as sa: select * from sys.syslogins where name = 'MyDomain\DomainUserA'
*Result: No records found.
5. Ran as sa: select * from sys.server_principals where name = 'MyDomain\DomainUserA'
*Result: No records found.
6. Ran as sa: SELECT SUSER_SID('MyDomain\DomainUserA')
*Result: 0x010500000000000515000000358A021A0D7A5A3307E53B2B8A280000
7. Ras as sa: select * from sys.syslogins where sid = '0x010500000000000515000000358A021A0D7A5A3307E53B2B8A280000'
*Result: No records found.
8. select * from sys.server_principals where sid = '0x010500000000000515000000358A021A0D7A5A3307E53B2B8A280000'
*Result: No records found.
I simply can't add this user back!
Your EXPERT assistance is most appreciated! If I am being a NOOB, feel free to tell me! 😀
As you can imagine, this is causing some frustration and I am unable to continue in many regards with what I am trying to accomplish.
Kind regards,
Nick
May 10, 2011 at 7:28 pm
You can check the below query.
select * from sys.server_principals
where sid = (SELECT SUSER_SID('MyDomain\DomainUserA'))
This would probably give you a different login name than 'MyDomain\DomainUserA'
You would probably have to drop the login and create the required login again.
Hope this helps.
May 11, 2011 at 7:38 am
Yes, as Mohammed has posted, check to see if there is an account already added with that SID. In the case where the user or group name is changed in Active Directory, the SID does not change (so long as it remains in the same domain). This can lead to the exact issue you're experiencing. However, the error message doesn't clearly indicate what the issue is.
If that's the issue, the reason it's failing is when SQL Server goes to check the account against Active Directory, it gets the SID back. It then compares the SID to what it already has. If it finds a match, regardless of whether the name matches, it throws that error.
K. Brian Kelley
@kbriankelley
May 11, 2011 at 10:13 am
April 14, 2015 at 7:33 pm
How was the problem resolved? How to remove an unwanted and orphaned PRINCIPAL??
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply