September 20, 2011 at 10:19 am
I've implemented System Center Virtual Machine Manager (SCVMM) 2008 R2 which uses a sql express 2005 db by default and I'm trying to build some queries off it for reporting purposes. One of the queries I want to develop uses the table tbl_RBS_UserRole and the Owner column which is a varbinary. My expectation of this column, based on the UI, is that this should store the AD Username or a pointer to it or something. When looking at a varbinary column it's not human readable and short of manually determining which user is what and creating and maintaining a lookup table (not real interested in doing that based on the sheer number of users) I can't seem to make these columns useful. Is there some trick to using varbinaries? Why would MS store what seems like a string in a varbinary column?
September 20, 2011 at 10:38 am
I am not familiar with that product and never heard of that table but, can you cast that column as a varchar?
select cast(Owner as varchar(max)) as Owner from tbl_RBS_UserRole
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 20, 2011 at 10:42 am
Ok, so I did try a convert on that table so I was on the right path, but I didn't get the result I was expecting:
select convert(varchar(100), Owner) from tbl_RBS_UserRole
This resulted in the column outputting gibberish:
MJNŒC§!*=AO
So perhaps MS is actually storing this as a non-queryable format.
Using cast gives the same result.
September 20, 2011 at 12:43 pm
It's probably AD credential information. If so, it's hashed for security reasons.
You can reverse-engineer the hashes easily enough, or pull in the AD data and query against that. But you can't just convert it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 20, 2011 at 1:01 pm
Good to know, think you can point me in the right direction?
September 20, 2011 at 1:06 pm
Fire up a search (Boogle, Ging, whatever) for "query active directory in sql server". There are a bunch of good articles available on the subject.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply