5.4 Functions

5.4.1 srs.access_mask_basic_string

Parameters

SQL Server

PostgreSQL

@mask

integer

integer

@path_type

integer

integer

Return Value

nvarchar(128)

varchar(128)

Description: Converts an NTFS access mask value to its basic permissions string equivalent.

Note that the values displayed here are functionally equivalent to what is seen in the primary window of the security tab for an NTFS file system entry.

  • Entries having permissions that do not fit the basic permissions (such as Special permissions) include an asterisk *.

  • The path_type is required since the same flags represent different semantic values for folders, files and shares. Path type must be one of 1 (file), 2 (folder) or 7 (share).

  • Permissions flags are mapped to one or more of the following values:

    • Full Control

    • Modify

    • Read & Execute

    • List Folder Contents (Folders only)

    • Read

    • Write

    • Special Permissions

    Example (SQL Server)

    Example (PostgreSQL)

5.4.2 srs.access_mask_string

Parameters

SQL Server

PostgreSQL

@mask

integer

integer

@path_type

integer

integer

Return Value

nvarchar(128)

varchar(128)

Description: Converts an NTFS access mask value to its advanced permissions string equivalent.

Note that the values displayed here are functionally equivalent to what is seen in the advanced section of the security tab for an NTFS file system entry.

  • The path_type is required since the same flags represent different semantic values for folders, files and shares. Path type must be one of 1 (file), 2 (folder) or 7 (share).

  • Flags correspond to the following values:

    0x00000001

    Rd / Lf

    Read data / List folder

    0x00000002

    Wd / Cf

    Write data / Create file

    0x00000004

    Ad / Cs

    Append data / Create subdirectory

    0x00000008

    Rx

    Read extended attributes

    0x00000010

    Wx

    Write extended attributes

    0x00000020

    Xf / Tf

    File execute / Traverse

    0x00000040

    Ds

    Delete child (subdirectory)

    0x00000080

    Ra

    Read attributes

    0x00000100

    Wa

    Write attributes

    0x00010000

    De

    Delete

    0x00020000

    Rp

    Read permissions

    0x00040000

    Cp

    Change permissions

    0x00080000

    To

    Change owner (take ownership)

    0x00100000

    Sy

    Synchronize

    0x01000000

    Ss

    Access system security

    0x10000000

    Ga

    Generic All

    0x20000000

    Ge

    Generic Execute

    0x40000000

    Gw

    Generic Write

    0x80000000

    Gr

    Generic Read

Example (SQL Server)

Example (PostgreSQL)

5.4.3 srs.ace_flags_string

Parameters

SQL Server

PostgreSQL

@flags

integer

integer

Return Value

nvarchar(128)

varchar(128)

Description: Converts the access mask flags to a string representation. Flags are converted as follows:

0x001

(OI)

Object inherit

0x002

(CI)

Container inherit

0x004

(NP)

No propagate

0x008

(IO)

Inherit only

0x010

(ID)

Inherited

0x040

(SA)

Successful access

0x080

(FA)

Failed access

Example (SQL Server)

Example (PostgreSQL)

5.4.4 srs.ace_type_string

Parameters

SQL Server

PostgreSQL

@ace_type

integer

integer

Return Value

nvarchar(128)

varchar(128)

Description: Converts the access mask type value to a corresponding text value.

Flags correspond as follows:

0

Access Allowed

1

Access Denied

2

System Audit

3

System Alarm

4

Allowed Compound

5

Allowed Object

6

Denied Object

7

System Audit Object

8

System Alarm Object

9

Allowed Callback

10

Denied Callback

11

Allowed Callback Object

12

Denied Callback Object

13

System Audit Callback

14

System Alarm Callback

15

System Audit Callback Object

16

System Alarm Callback Object

17

System Mandatory Label

For NTFS file systems the primary values of concern are Allowed (0), Denied (1), Audit (2), and System Mandatory Label (17).

Example (SQL Server)

Example (PostgreSQL)

5.4.5 srs.ad_account_name

Parameters

SQL Server

PostgreSQL

@domain

nvarchar(1024)

varchar(1024)

@name

nvarchar(1024)

varchar(1024)

@sid

binary(68)

bytea

Return Value

nvarchar(max)

text

Description: Description: Converts primary naming values for an Windows security principal to a display name.

  • If domain is null or empty, the leading backslash is not included in the result

  • If the name is null or empty, the result value is the SDDL sid representation

  • If the sid is needed but is invalid, the return value is [Invalid SID]

Example - Domain and Name

Example - SID

5.4.6 srs.attribute_string

Parameters

SQL Server

PostgreSQL

@flags

integer

integer

Return Value

nvarchar(256)

varchar(256)

Description: Converts an attributes value to its equivalent string representation. Flags correspond to the following values:

0x00000000

 

None

0x00000001

Ro

Read Only

0x00000002

Ar

Archive

0x00000004

Sy

System

0x00000008

Hi

Hidden

0x00000010

Dr

Directory

0x00000020

Co

Compressed

0x00000040

Ol

Offline

0x00000080

De

NTFS device

0x00000100

No

NTFS Normal

0x00000200

Te

NTFS Temporary

0x00000400

Sp

NTFS Sparse File

0x00000800

Rp

NTFS Reparse Point

0x00001000

Nc

NTFS Not content indexed

0x00002000

En

NTFS Encrypted

0x00004000

Vi

NTFS Virtual

Example (SQL Server)

Example (PostgreSQL)

5.4.7 srs.byte_string

Parameters

SQL Server

PostgreSQL

@size

bigint

bigint

Return Value

nvarchar(64)

text

Description: Converts a size value to a string representation of the closest unit.

  • The return value has a maximum precision of two decimal places.

  • Units include kilobyte (KB), megabyte (MB), gigabyte (GB), terabyte (TB), petabyte (PB), and exabyte (EB).

Example

5.4.8 srs.byte_unit_string

Parameters

SQL Server

PostgreSQL

@size

bigint

bigint

@unit

nvarchar(10)

varchar(10)

@precision

integer

integer

Return Value

nvarchar(64)

text

Description: Converts a number to a string representation of the specified unit with the specified precision.

  • The specified precision is limited to a value from 0 to 3. Values outside this range will be adjusted to 0 or 3 accordingly.

  • Unit specifiers are case insensitive and include:

    • byte

    • KB (kilobyte)

    • MB (megabyte)

    • GB (gigabyte)

    • TB (terabyte)

    • PB (petabyte)

    • EB (exabyte)

5.4.9 srs.bytes_to_hex_string

Parameters

SQL Server

PostgreSQL

@byte_sequence

varbinary(max)

bytea

Return Value

nvarchar(max)

text

Description: Converts a byte sequence to its equivalent hex string representation.

  • Returned hex string is lower case with no separators and no prefix.

Example

5.4.10 srs.guid_bytes

Parameters

SQL Server

PostgreSQL

@guid_text

nvarchar(38)

varchar(38)

Return Value

varbinary(16)

bytea

Description: Converts a compatible GUID text string to its equivalent binary representation.

Recommended input format: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

  • Surrounding curly braces are optional

  • Hex values A-F may be in upper or lower case

  • Hyphen separators must be present at the specified 4 locations or not at all

Example

5.4.11 srs.guid_text

Parameters

SQL Server

PostgreSQL

@guid_binary

varbinary(16)

bytea

Return Value

nvarchar(36)

varchar(36)

Description: Converts a binary GUID value to its equivalent string representation.

Note that returned strings are in the canonical lower-case GUID format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.

Example

5.4.12 srs.hex_string_to_bytes

Parameters

SQL Server

PostgreSQL

@byte_sequence

varbinary(max)

bytea

Return Value

nvarchar(max)

text

Description: Converts a hex string to its equivalent bytes.

  • Hex values A-F may be in upper or lower case.

  • Hex string must be a proper string with an even number of characters — leading zeros are required for each hex value having a single digit.

  • Do not include separators such as hyphens between hex values.

Example

5.4.13 srs.path_hash

Parameters

SQL Server

PostgreSQL

@path

nvarchar(max)

text

Return Value

binary(20)

bytea

Description: Returns the binary SHA-1 hash for a given path.

  • The input path is first converted to lower case

  • The input path is then converted to byte representation using the default text encoding of the database for string values (typically UTF-8 on PostgreSQL, and Unicode UCS-2 on SQL Server)

  • Useful for finding a path in the srs.scan_data table using the fullpath_hash indexed column

Example

5.4.14 srs.path_hash_sha256

Parameters

SQL Server

PostgreSQL

@path

nvarchar(max)

text

Return Value

binary(32)

bytea

Description: Returns the binary SHA256 hash for a given path.

  • The input path is first converted to lower case

  • The input path is then converted to byte representation using the default text encoding of the database for string values (typically UTF-8 on PostgreSQL, and Unicode UCS-2 on SQL Server)

  • Useful for finding a path (web URL) in the ms365.drive_items table using the web_url_hash indexed column

Example

5.4.15 srs.sid_bytes

Parameters

SQL Server

PostgreSQL

@sid

nvarchar(256)

varchar(256)

Return Value

varbinary(68)

bytea

Description: Converts an SDDL representation of a Security Identifier value to its binary form.

  • Input SID values must be in proper SDDL form

Example

5.4.16 srs.sid_text

Parameters

SQL Server

PostgreSQL

@sid_bytes

varbinary(68)

bytea

Return Value

nvarchar(256)

varchar(256)

Description: Converts a binary Security Identifier to its SDDL string representation.

Example