Can you Convert/Cast SID to String???

Question

Post reply

Can you Convert/Cast SID to String???

Topher

SSCommitted

Points: 1763
More actions
May 29, 2009 at 2:52 pm

#138177

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.

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

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

Robert Klimes SSCoach Points: 17865 More actions · Answer 1

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

http://qa.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]

Topher SSCommitted Points: 1763 More actions · Answer 2

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:

Robert Klimes SSCoach Points: 17865 More actions · Answer 3

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]

Topher SSCommitted Points: 1763 More actions · Answer 4

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...

Robert Klimes SSCoach Points: 17865 More actions · Answer 5

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]

Topher SSCommitted Points: 1763 More actions · Answer 6

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

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

Florian Reischl SSC-Dedicated Points: 37299 More actions · Answer 7

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.

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

Topher SSCommitted Points: 1763 More actions · Answer 8

Topher

SSCommitted

Points: 1763

June 4, 2009 at 7:46 am

#1004636

thanks for the headsup, Florian!