I was recently writing a report in SSRS that required me to display a SID – a nasty little piece of data normally represented as a
varbinary(85), and that looks like an enormous hex number.
I wrote my query in the usual way:
SELECT 'ServerRole' = spv.name, 'MemberName' = lgn.name, 'MemberSID' = lgn.sid FROM master.dbo.spt_values spv, master.dbo.sysxlogins lgn WHERE spv.low = 0 AND spv.type = 'SRV' AND lgn.srvid IS NULL AND spv.number & lgn.xstatus = spv.number
(Those of you who are extremely geeky will notice that this is (basically) the contents of
sp_helpsrvrolemember in SQL Server 2000…) The results:
I was somewhat perturbed when the draft report layout came back with #ERROR in the MemberSID field, as the results posted back by a query tool (above) look just fine.
I tried changing the SELECT statement to use CONVERT (or CAST), but to no avail – they made things look bad in SSMS’s query results window!
And it looks really bad if you switch to the Text view, with even the resultset font being messed around:
A little bit of googling pointed me in the direction of the undocumented function:
fn_varbintohexstr. Put that into the
SELECT statement in the appropriate place:
SELECT 'ServerRole' = spv.name, 'MemberName' = lgn.name, 'MemberSID' = master.dbo.fn_varbintohexstr(lgn.sid) FROM master.dbo.spt_values spv, master.dbo.sysxlogins lgn WHERE spv.low = 0 AND spv.type = 'SRV' AND lgn.srvid IS NULL AND spv.number & lgn.xstatus = spv.number
And, at last, SSRS shows what I need it to show: