SQL Active Directory User ID SID Mismapings
Sometimes login mapping issues exist where you least expect them
This is not the traditional SQL login SID mismap issue that is frequently encountered and discussed here. This mismap was a new one on me so I thought id document it.
When trying to add a new Windows login for SQL Server 2005 or 2008 (probably earlier versions but not tested) you may wind up with this error message
Msg 15025, Level 16, State 2, Line 1
The server principal 'DXXXX\UXXX' already exists.
Contrary to the error message, If we need to proove the account doesnt exist, the following code should suffice
select name from sys.server_principals where name = ‘DXXXX\UXXX’
SQL wont let you create an account because it thinks it already exists, but clearly the account doesnt exist.
How did this happen?
If a login was created previously for a windows account that has since been renamed in the active directory you cannot grant the new userid access to the SQL server because the SID already exists in SQL and you cannot duplicate it. This occurs when renaming an account in Active directory, because the SID is reused instead of recreated.
Find the pre-existing SID
The SID from Active directory can be obtained many ways, the one I use is PsGetsid which can be obtained here the syntax you’d use for this is
PsGetSid DOMAIN\USER (or DXXXX\UXXX from earlier)
This will return the SID in the SDDL Format of S-x-x-x-x
Unfortunately SQL Stores the binary representation of the SID not the SDDL format. You have several choices to do the mapping, you can hand figure out the SDDL mappings from sys.server_principals using this page Or the better way -> Todd Engen had found some need to convert this before and designed a great function to do this conversion from binary to SDDL , im only going to list it here for completeness, the original is found here
CREATE FUNCTION fn_SIDToString
@BinSID AS VARBINARY(100)
IF LEN(@BinSID) % 4 <> 0 RETURN(NULL)
DECLARE @StringSID VARCHAR(100)
DECLARE @i AS INT
DECLARE @j AS INT
SELECT @StringSID = 'S-'
+ CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 1, 1))))
SELECT @StringSID = @StringSID + '-'
+ CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 3, 6))))
SET @j = 9
SET @i = LEN(@BinSID)
WHILE @j < @i
DECLARE @val BINARY(4)
SELECT @val = SUBSTRING(@BinSID, @j, 4)
SELECT @StringSID = @StringSID + '-'
+ CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @val)))))
SET @j = @j + 4
RETURN ( @StringSID )
After creating this function use it like so
select name from sys.server_principals where dbo.fn_SIDToString(sid)=‘S-X-X-X-X’ where the ‘S-X-X-X-X’ is the SID obtained earlier from PsGetSid
This should return the name of the user that has the offending “duplicate” SID
Once you know the logon ID that has been renamed you can script out the permissions of that ID (hopefully it doesn’t own any objects), drop it, and recreate it with the appropriate name and grant any additional permissions
|Print article||This entry was posted by Allen Kinsel on February 17, 2010 at 8:10 am, and is filed under Security, SQL Server, Syndicated. Follow any responses to this post through RSS 2.0. You can leave a response or trackback from your own site.|
No trackbacks yet.
about 1 year ago - 1 comment
Came across a strange permission issue today that I thought I’d blog about since it is apparently somewhat common judging by the number of unanswered forum posts about it.
Adding/editing or Testing SSRS Datasources in Report Manager generates a permission error
A developer was trying to add a datasource and use “test connection” in the report manager web interface instead of BIDS which, incidentally
about 1 year ago - No comments
Earlier this week I was working through an issue that I’ve seen one too many times lately and thought Id write about it. For various reasons *cough* Legacy *cough* I often seem to run across columns in tables that are defined with what would appear to be the wrong data type or worse, multiple data
about 2 years ago - 40 comments
Its the first week of June and for those of us living along the Gulf and Atlantic coasts of the US, that brings the beginning of hurricane season. It also means its time for this months installment of T-SQL Tuesday.
This Months Topic
Disaster Recovery. This topic is very near and dear to me based on the fact that
about 3 years ago - 11 comments
After last years Summit we launched a feedback site http://feedback.sqlpass.org in hopes of gathering all of the feedback about the event in one place. The number one thing people have asked for on that site is for there to be a track of sessions in the 400-500 level range. The problems with the community desire
about 3 years ago - 2 comments
Occasionally I’m asked what the most important thing for a DBA to know is. It seems some people want to know what 1 thing to focus on in order to be successful long term as a DBA. I’ve pondered this question for quite a long time and I’ve given various answers over the years.
about 3 years ago - 5 comments
This months TSQL Tuesday is hosted by a good friend Pat right over at SQL Asylum
For this months entry I decided to keep it short and sweet, following in my Bits N Bytes theme.
The Meta Script
In the true sense of the word automation, this really doesn’t fit but, in the terms of quickly getting something
about 3 years ago - 4 comments
This corruption story begins like many. Somebody in a server room far far away decided to make a change to a VMware guest machine and that little change rippled through our poor server like a lady Gaga Meat Dress through the VMA’s. Needless to say, it wasnt pretty. The full set of events may never
about 3 years ago - 1 comment
The other day we were going through some old scripts and had a huge discussion about changing object ownership in SQL. Forever the only answers were to recreate the objects or use the stored procedures: sp_changeobjectowner or sp_changedbowner. Alternatively at times there were those of us who used some other undisclosed hack of system tables
about 3 years ago - No comments
For the new year I have decided to try something different with a series of small regular posts.
Dealing with our large team of developers and our DBA team as well, I often run across interesting discussions about small things that are easily overlooked. Often times these discussions end in a exclamation of “I knew that!!” Of
about 3 years ago - 1 comment
And no, I dont mean in my mothers eyes, thats a given.
I won the Ipad/SQL Monitor Contest that Redgate was sponsoring over on Grant Fritcheys Blog
Im thrilled to have won!! Ive been eyeing Ipads for quite some time but, the novelty & cost has kept me at arms length.
Im always in the market for DBA tools that can make