Image Field to Varchar Field

Transact-SQL
1

Does anyone know how to translate the image field NtSecDescPrimary? I would like to know what is in this field.

USE ReportServer
SELECT
  SecDataID
, PolicyID
, AuthType
, XmlDescription
, NtSecDescPrimary
, NtSecDescSecondary
FROM SecData

So far, I have tried this but not the correct data.

--SELECT CAST(CAST(CAST('SQL Server' AS IMAGE) AS VARBINARY(MAX)) AS VARCHAR(MAX))

SELECT
	  aS1.*  
	, CAST(CAST(NtSecDescPrimary AS VARBINARY(MAX)) AS VARCHAR(MAX))
FROM [dbo].SecData aS1
2

why, what is the business case for this?

3

It is complicated to explain because it is better to walk you through visually. But the objective is to control the report security through automated scripts.

So far, I have been able to update users and roles in the XML and tables correlation. In order for this to complete, I would have to go to the dashboard and MANUALLY add/delete some bogus user from the chosen report and then both the XML and Policy tables sync which the security then works.

The fields NtSecDescPrimary and NtSecDescSecondary are in questioned as to completely remove the MANUAL step above.

4
  1. Waiting for the visual walk through
  2. This is a most unusual approach. Usually you add security to reports by assigning users to ad groups that have access to these reports etc and not updating what I would term system databases such as RepprtServer. Doable yes but best practice I am not sure. Makes me nervous.
1 Like
5

Hope this helps :slight_smile: :slight_smile:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/253afd58-2d62-4df7-baba-beaaaa24ec41/how-to-read-the-value-of-image-data-type-to-plain-text?forum=transactsql