Can you Convert/Cast SID to String???

  • I have a VB app that I'm grabbing SIDs from Syslogins/users. But, when I grab it out of SQL, and try to covert it to either a string or byte, it yells at me. So, I was thinking of handling this at the SQL query level but cannot figure it out.

    But, is is possible to select sid from mater..syslogins and covert the SID to a string value.

  • perhaps this will help. It is quite what you are looking for but may be of value.

    http://www.sqlservercentral.com/scripts/SID/62274/[/url]

    I must ask, what would you need the actual sid value for?

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • Ah, I seen that funct too. I was hoping to do it w/ soley a sql query.

    I'm grabbing all the data from these systems tables from all the sql severs I own and gathering them in a single location. That way, I can report on all the sql servers rather than connecting to 900 of them.... :crazy:

  • I assume the single location will be a database so why the need to convert the value to a string? Why not just leave it as varbinary?

    I only ask this because the only way I can get these values as a string is to output results to a file then import that file.

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • Here is my issue, in my vb.net app, it does a query from Syslogins/users. When the app runs this query, it tries to throw each column in a variable, when doing so, it yells at me that cannot convert byte to string. Even when I declare the var as byte, it still yells cannot convert byte to byte. So, that's why I asked if this can be done at the SQL side rather than messing w/ it at the vb side...

  • don't know if this will work for you but...

    exec xp_cmdshell 'bcp "select sid from master.sys.syslogins" queryout c:\bcpout.txt -T -c';

    go

    create table sidtemp([sid] varchar(255));

    go

    exec xp_cmdshell 'bcp master.dbo.sidtemp in c:\bcpout.txt -T -c';

    go

    select * from sidtemp;

    I'm sure, through VB, you can read directly from the file thus negating the need to create and load table from file.

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • If anyone finds this down the road, found the *easy* solution...

    SELECT SUBSTRING(master.dbo.fn_varbintohexstr(sid),0,88) from master..syslogins

  • Be careful with master.sys.fn_varbintohexstr. It is a undocumented function and MS changed in SSE2k8 (return value becomes VARCHAR(MAX)). It might be changed/renamed/removed in future versions.

  • thanks for the headsup, Florian!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply