How to convert binary valude to string? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How to convert binary valude to string?

Author

Topic

pszivos
Starting Member

10 Posts

Posted - 2006-10-30 : 03:12:44

Hi,

I'd need to construnct an EXEC statement in my script and I am pretty much stuck with data conversion. I need to compare a binary variable with a SID value to an actual SID found in ..sysusers.

When I construct the EXEC statement the whole line has to be string and I need to find a way to use the binary data as string.

So far, I tried to play with CONVERT and CAST, but could not succeed.

A simplified version of what I tried to do:

declare @t_sid binary(85)
select @t_sid = 0x01050000000000051500000043170A324D64492E235F636B 18BA0400000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 0000000000000000000000
print convert(nvarchar(10), @t_sid)

This results a nice square character, nothing else...

Any I idea dear experts?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 03:48:58

http://www.codeproject.com/system/sid.asp
http://windowssdk.msdn.microsoft.com/en-us/library/ms718705.aspx
http://support.microsoft.com/kb/286182/EN-US/

Peter Larsson
Helsingborg, Sweden

pszivos
Starting Member

10 Posts

Posted - 2006-10-30 : 03:51:29

Thank you Peter!

I did not indicate, but I need to solve this issue in T-SQL: using SID stored in system tables of databases.

Peter Szivos
Lausanne, Switzerlan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 03:54:51

Then just do a = thingy...
T-SQL is able to check for equality between binary value.

It would help a little more if you give us more information about what you are trying to accomplish and which values you have.

Peter Larsson
Helsingborg, Sweden

pszivos
Starting Member

10 Posts

Posted - 2006-10-30 : 04:47:37

OK, I am checking each database if a user (login) is defined or not. As I cannot do a select with a variable (actual database name in it) like

select * from @database..sysusers where @database..sysusers.sid = @sid_actual (previously read from MASTER..SYSLOGINS.SID)

I am trying to do a big botchwork:

EXEC ('USE ['+@db_act+'] SELECT * FROM [' + @db_act + ']..sysusers WHERE [' + @db_act + ']..sysusers.sid = '+ @t_sid <= This is where I need to refer to the binary value as string...

Comments:
@db_act - name of the actual DB it is checking
@t_sid - sid of the login read previously form MASTER..SYSLOGINS.SID

If the checking is successful, I generate some text file with some commands...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 04:58:51

[code]select * from sysusers where sid = 0x0105000000000005150000007CEB240DD931F84243170A32EB030000[/code]works for me...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 04:59:35

Maybe you are just missing the "0x" in front?

Peter Larsson
Helsingborg, Sweden

pszivos
Starting Member

10 Posts

Posted - 2006-10-30 : 04:59:39

Yes, this one works nicely for me, too. But, ask I said I go throught each database and the names are referred to from a variable.

How do you do a USE @databasename? I could not, this is why I started to construct an EXEC command where I have to operate with strings!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 05:05:55

Try this code of mine

/*
select * from test..sysusers

select * from master..syslogins

select * from master..sysdatabases
*/

create table #db (id int identity(0,1), name sysname)

insert	#db (name)
select	name
from	master..sysdatabases
--where	sid <> 0x01

create table #logins (name sysname, sid varbinary(85))

insert	#logins (name, sid)
select	name,
	sid
from	master..syslogins
where	sid <> 0x01

create table #users (dbname sysname null, loginsid varbinary(85), username sysname)

declare @id int,
	@sql varchar(1000),
	@dbname sysname

select	@id = max(id)
from	#db

while @id >= 0
	begin
		select	@dbname = name
		from	#db
		where	id = @id

		select @sql = 'insert #users (loginsid, username) select sid, name from ' + quotename(@dbname) + '..sysusers where sid is not null'
		exec (@sql)

		update	#users set dbname = @dbname where dbname is null

		select @id = @id - 1
	end

drop table #db

-- Logins that are connected to a database
select		'Logins that are connected to a database',
		#users.dbname 'Database name',
		#logins.name 'Login name'
from		#users
inner join	#logins on #logins.sid = #users.loginsid

-- Logins that are not connected to a database
select		'Logins that are not connected to a database',
		#logins.name 'Login name'
from		#logins
left join	#users on #users.loginsid = #logins.sid
where		#users.loginsid is null

-- Users for a database that not have a login
select		'Users for a database that not have a login',
		#users.dbname 'Database name',
		#users.username 'User name'
from		#users
left join	#logins on #logins.sid = #users.loginsid
where		#logins.sid is null

drop table #logins
drop table #users

Peter Larsson
Helsingborg, Sweden

pszivos
Starting Member

10 Posts

Posted - 2006-10-30 : 08:45:19

Thank you very much! I saved the logins into a tmp table and used it like suggested. It works!

Regards,
Peter

Niikola
Starting Member

1 Post

Posted - 2009-02-05 : 03:46:21

quote:
...need to find a way to use the binary data as string...

Convert binary values to character string


Print Convert(varchar(64), 0x4665656442656566, 0)
Print Convert(varchar(64), 0x4665656442656566, 1)
Print Convert(varchar(64), 0x4665656442656566, 2)

Results:


FeedBeef
0x4665656442656566
4665656442656566

Convert character string to binary


Print Convert(varbinary(64), 'FeedBeef', 0)
Print Convert(varbinary(64), '0xFeedBeef', 1)
Print Convert(varbinary(64), 'FeedBeef', 2)

Results:


0x4665656442656566
0xFEEDBEEF
0xFEEDBEEF

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources